Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reordering text and numbers in a cell
I have a column of data (G) which has people's names and roll numbers in it.
These are user input and although the correct format is 123456 John Smith there are instances where the name is before the number or there is no number or name e.g. 123456 John Smith Bill Bobbins 987654 Fred Figgis 438764 Mike Morris 907856 I need to reformat the data so if there's a number it always appears before the text so the above examples would show as: 123456 John Smith 987654 Bill Bobbins Fred Figgis 438764 Mike Morris 907856 I haven't been able to do this formulaically yet so I'm really struggling to find a manual solution. Any help would be greatly appreciated. Thanks a lot |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reordering text and numbers in a cell
Anybody named John Smith, 3rd
i.e. Anybody have numbers in their name? Are roll numbers always 6 digits if they exist? -- Regards, Tom Ogilvy "nospaminlich" wrote in message ... I have a column of data (G) which has people's names and roll numbers in it. These are user input and although the correct format is 123456 John Smith there are instances where the name is before the number or there is no number or name e.g. 123456 John Smith Bill Bobbins 987654 Fred Figgis 438764 Mike Morris 907856 I need to reformat the data so if there's a number it always appears before the text so the above examples would show as: 123456 John Smith 987654 Bill Bobbins Fred Figgis 438764 Mike Morris 907856 I haven't been able to do this formulaically yet so I'm really struggling to find a manual solution. Any help would be greatly appreciated. Thanks a lot |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reordering text and numbers in a cell
Sorry Tom I should have been more clear.
There should be 6 numbers but there are sometimes more or less. In addition there are sometimes unnecessary spaces and dashes between the sequences of letters/numbers. I've been working (unsuccessfully!) on trying to find the first number in the cell followed by another number then the first letter not followed by a number then using those positions to reorder the info. Thanks a lot for looking at this. "Tom Ogilvy" wrote: Anybody named John Smith, 3rd i.e. Anybody have numbers in their name? Are roll numbers always 6 digits if they exist? -- Regards, Tom Ogilvy "nospaminlich" wrote in message ... I have a column of data (G) which has people's names and roll numbers in it. These are user input and although the correct format is 123456 John Smith there are instances where the name is before the number or there is no number or name e.g. 123456 John Smith Bill Bobbins 987654 Fred Figgis 438764 Mike Morris 907856 I need to reformat the data so if there's a number it always appears before the text so the above examples would show as: 123456 John Smith 987654 Bill Bobbins Fred Figgis 438764 Mike Morris 907856 I haven't been able to do this formulaically yet so I'm really struggling to find a manual solution. Any help would be greatly appreciated. Thanks a lot |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
reordering text and numbers in a cell
Hi,
Hopefully this might get you started - I don't guarantee it covers all conditions. It delimits data by blanks. Function Reorder(inputStr) Dim v As Variant Dim i As Integer Dim aStr As String v = Split(inputStr, " ") aStr = "" For i = 0 To UBound(v) If trim(v(i) )< "" Then If IsNumeric(v(i)) Then aStr = Trim(v(i)) & " " & aStr Else aStr = aStr & " " & Trim(v(i)) End If End If Next i Reorder = Trim(aStr) End Function Assume data in A1, then in B1 =Reorder(A1) This some test data I used - input on left, output on right 123 John Smith 123 John Smith Fred Brown 45678 45678 Fred Brown Mike Davis Mike Davis 98760 Ian Wright 98760 Ian Wright 78965 78965 Fred Brown 45678 45678 Fred Brown Fred Brown - 3rd 45679 45679 Fred Brown - 3rd Fred Brown 3rd 45680 45680 Fred Brown 3rd Fred Brown 3rd45681 Fred Brown 3rd45681 *** problem! Fred Brown , 3rd 45682 45682 Fred Brown , 3rd Fred Brown, 3rd 45683 45683 Fred Brown, 3rd HTH "nospaminlich" wrote: Sorry Tom I should have been more clear. There should be 6 numbers but there are sometimes more or less. In addition there are sometimes unnecessary spaces and dashes between the sequences of letters/numbers. I've been working (unsuccessfully!) on trying to find the first number in the cell followed by another number then the first letter not followed by a number then using those positions to reorder the info. Thanks a lot for looking at this. "Tom Ogilvy" wrote: Anybody named John Smith, 3rd i.e. Anybody have numbers in their name? Are roll numbers always 6 digits if they exist? -- Regards, Tom Ogilvy "nospaminlich" wrote in message ... I have a column of data (G) which has people's names and roll numbers in it. These are user input and although the correct format is 123456 John Smith there are instances where the name is before the number or there is no number or name e.g. 123456 John Smith Bill Bobbins 987654 Fred Figgis 438764 Mike Morris 907856 I need to reformat the data so if there's a number it always appears before the text so the above examples would show as: 123456 John Smith 987654 Bill Bobbins Fred Figgis 438764 Mike Morris 907856 I haven't been able to do this formulaically yet so I'm really struggling to find a manual solution. Any help would be greatly appreciated. Thanks a lot |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
reordering text and numbers in a cell
Thanks a lot. That looks excellent. I appreciate that it's impossible to
legislate for every permutation but this will do nicely. Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
copy numbers from numbers+text cell | Excel Worksheet Functions | |||
Extract numbers from cell with Text and Numbers | New Users to Excel | |||
How do I extract numbers from a cell with both text and numbers? | Excel Worksheet Functions | |||
extract numbers from cell containing text & numbers | Excel Worksheet Functions |