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

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

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

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

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