Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!

## How to split numbers and text from a string in Excel

I received an e-mail from Marc, in which he asked me whether it is posible to split numbers and text in Excel. The answer of the solution is based upon determining the first position of a number in a string, you can do that by using the following formula.

=MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9})) |

The next trick is to determine the **text within a string**:

**A101** will become **A** when using the following formula

=LEFT(A1;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))-1)) |

**101A** will become **A** when using the following formula

=RIGHT(A2;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9})))) |

To determine the **numbers within a string**:

**A101** will become **101** when using the following formula

=RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1) |

**101A** will become **101** when using the following formula

=LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))) |

You might want to convert the text string to numbers by using the following formula.

=VALUE() |

**A101** will become numeric value **101** when using the following formula

=VALUE(RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1)) |

**101A** will become numeric value **101** when using the following formula

=VALUE(LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9})))) |

Suggestions for improving this article are welcome, please let me know and drop me a line.

## Rearrange columns in Excel using VBA

Did you ever need to rearrange or reorganize columns across multiple sheets in a certain order based on column headers? In this article I’ll try to explain how to rearrange columns in Excel based on column header information by using Visual Basic for Applications (VBA) .

As mentioned in the intro, this article is about rearranging columns in Excel using column header information.

To make it more visible see the images below…

In this example the headers are in alpabetical order:

Address, City, Country, Date of Birth, First Name, Last Name, Middle Name, Phone Number, Postal (ZIP) Code, State.

And let’s say you want to change the column order to:

First Name,Last Name, Middle Name, Date of Birth, Phone Number,Address, City, State, Postal (ZIP) Code, Country (see the image below)

For those of you that are not familiar with VBA / macro’s use the steps below…

- First make sure you’ve got the “Developer” tab in Excel
- Next press the button “Record Macro”
- Then press the button “Stop Recording”
- Press ALT + F8
- Choose “Edit”
- Copy and paste the code below (or download a text file containing the code here…)
- You might want to use your own headers and ordering, so change the code there 😉
- Save your Excel !!!
- Press ALT + F8 and Run the Macro

Sub MoveColumns() ‘ MoveColumns Macro ‘ ‘ Developer: Winko Erades van den Berg ‘ E-mail : winko at winko-erades.nl ‘ Developed: 03-10-2011 ‘ Modified: 03-10-2011 ‘ Version: 1.0 ‘ ‘ Description: Rearrange columns in Excel based on column headerDim iRow As Long Dim iCol As Long’Constant values data_sheet1 = InputBox(“Specify the name of the Sheet that needs to be reorganised:”) ‘Create Input Box to ask the user which sheet needs to be reorganised target_sheet = “Final Report” ‘Specify the sheet to store the results iRow = Sheets(data_sheet1).UsedRange.Rows.Count ‘Determine how many rows are in use’Create a new sheet to store the results Worksheets.Add.Name = “Final Report” ‘Start organizing columns ‘Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns ‘Read the header of the original sheet to determine the column order ‘If a TargetColumn was determined (based upon the header information) then copy the column to the right spot Next iCol ‘Move to the next column until all columns are read End Sub |

Additional information 1

Someone sent me an alternative solution for reorganizing columns in Excel. The script makes use of the array function in Excel. It does a really nice job but beware, the code handles your data in a way that it does keep your original data structure.

Sub Reorganize_columns() ‘ Reorganize Columns Macro ‘ ‘ Developer: If you want to know, please contact Winko Erades van den Berg ‘ E-mail : winko at winko-erades.nl ‘ Developed: 11-11-2013 ‘ Modified: 11-11-2013 ‘ Version: 1.0 ‘ ‘ Description: Reorganize columns in Excel based on column headerDim v As Variant, x As Variant, findfield As Variant Dim oCell As Range Dim iNum As Long v = Array(“First Name”, “Middle Name”, “Last Name”, “Date of Birth”, “Phone Number”, “Address”, “City”, “State”, “Postal (ZIP) Code”, “Country”) For x = LBound(v) To UBound(v) findfield = v(x) iNum = iNum + 1 Set oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)If Not oCell.Column = iNum Then Columns(oCell.Column).Cut Columns(iNum).Insert Shift:=xlToRight End If Next x End Sub |

Additional information 2

Dennis Klenner from D&R Design Ltd wanted you to know “Header names are case sensitive”! Thank you for the remark Dennis 🙂

Suggestions for improving this article are welcome, please let me know and drop me a line .

## Convert MAC address in MS Excel or OO Spreadsheet

As I’m currently working a lot with Excel, I get a lot of questions like “How do I do this?” or “How do I do that?”. Yesterday someone asked me how to convert a data string to a MAC address. The standard format for printing MAC addresses in human-friendly form is six groups of two hexadecimal digits, separated by hyphens (-) or colons (:), in transmission order, e.g. 01-23-45-67-89-ab, 01:23:45:67:89:ab. In this article I’ll give you examples of how to add, remove or change the separator for MAC addresses in MS Excel or OO Spreadsheet.

Adding a seperator (change **0123456789ab** into **01-23-45-67-89-ab**)

=CONCATENATE(MID(A2;1;2);”-“;MID(A2;3;2);”-“;MID(A2;5;2);”-“;MID(A2;7;2);”-“;MID(A2;9;2);”-“;MID(A2;11;2)) =CONCATENATE(MID(A2;1;2);”:”;MID(A2;3;2);”:”;MID(A2;5;2);”:”;MID(A2;7;2);”:”;MID(A2;9;2);”:”;MID(A2;11;2)) |

An alternate solution was send in by Franco Magliaro see below:

=MID(A2,1,2)&”-“&MID(A2,3,2)&”-“&MID(A2,5,2)&”-“&MID(A2,7,2)&”-“&MID(A2,9,2)&”-“&MID(A2,11,2) =MID(A2,1,2)&”:”&MID(A2,3,2)&”:”&MID(A2,5,2)&”:”&MID(A2,7,2)&”:”&MID(A2,9,2)&”:”&MID(A2,11,2) |

Removing a separator (change **01-23-45-67-89-ab** into **0123456789ab**)

=CONCATENATE(MID(A2;1;2);MID(A2;4;2);MID(A2;7;2);MID(A2;10;2);MID(A2;13; 2);MID(A2;16;2)) |

or the easy way

=SUBSTITUTE(A2;”-“;””) =SUBSTITUTE(A2;”:”;””) |

Changing a separator (change **01-23-45-67-89-ab** into **01:23:45:67:89:ab**)

=CONCATENATE(MID(A2;1;2);”:”;MID(A2;4;2);”:”;MID(A2;7;2);”:”;MID(A2;10;2 );”:”;MID(A2;13;2);”:”;MID(A2;16;2)) =CONCATENATE(MID(A3;1;2);”-“;MID(A3;4;2);”-“;MID(A3;7;2);”-“;MID(A3;10;2 );”-“;MID(A3;13;2);”-“;MID(A3;16;2)) |

or the easy way 🙂

=SUBSTITUTE(A2;”-“;”:”) =SUBSTITUTE(A2;”:”;”-“) |

An alternate solution, to convert the Cisco 0123.4567.89ab MAC format to the F5 load balancer MAC format of 01:23:45:67:89:ab, was send in by Mike Wintrode. To add an extra twist, the F5 load balancer also removes the leading “0” in any pair, so for instance **00:14:0F:80:a3:01** would show as **0:14:f:80:a3:1**.

So Mike created the following:

=CONCATENATE(IF(MID($A2,1,1)=”0″,MID($A2,2,1),MID($A2,1,2)),”:”,IF(MID($A2,3,1)=”0″,MID($A2,4,1),MID($A2,3,2)),”:”,IF(MID($A2,6,1)=”0″,MID($A2,7,1),MID($A2,6,2)),”:”,IF(MID($A2,8,1)=”0″,MID($A2,9,1),MID($A2,8,2)),”:”,IF(MID($A2,11,1)=”0″,MID($A2,12,1),MID($A2,11,2)),”:”,IF(MID($A2,13,1)=”0″,MID($A2,14,1),MID($A2,13,2))) |

The basic premise utilizes an “IF” statement to check for a preceding “0” and if present to return the next character, and if not to return both characters.

Adam Heath was so kind to send me an Exel template which uses most of the above formulas

(thanks Adam!), which you can download here.

Suggestions for improving this article are welcome, please let me know and drop me a line.