letter and numbers substitute

"ibo" wrote in message
...
Hi there

I have a cell with letters and numbers. Like below:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Is it possible to formula for substitute first two numbers on the left and
other numbers on the right like this:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Thanks for advance.

Ibo


You can construct a formula to add spaces between the letters and the second
group of numbers so that the whole string will be 11 characters long:
=LEFT(A1,FIND(" ",A1,4))&REPT(" ",11-LEN(A1))&RIGHT(A1,LEN(A1)-FIND("
",A1,4))
However, the second set of numbers will only line up if you use a monospaced
font.

Assuming you have one space between each group, you can use somthing like this:
your data in A1 then (I broke the formula into several subformulas for simplicity)
In B1 =FIND(" ",A1)
In C1 =MID(A1,B1+1,LEN(A1)-B1+1)
In D1 =FIND(" ",C1)
In E1 =RIGHT(C1,LEN(C1)-D1)
In F1 =LEFT(C1,D1-1)
In G1 =LEFT(A1,B1-1)
In H1 =E1&" "&F1&" "&G1

Ilan



"Paul" none wrote in message ...
"ibo" wrote in message
...
Hi there

I have a cell with letters and numbers. Like below:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Is it possible to formula for substitute first two numbers on the left and
other numbers on the right like this:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Thanks for advance.

Ibo


You can construct a formula to add spaces between the letters and the second
group of numbers so that the whole string will be 11 characters long:
=LEFT(A1,FIND(" ",A1,4))&REPT(" ",11-LEN(A1))&RIGHT(A1,LEN(A1)-FIND("
",A1,4))
However, the second set of numbers will only line up if you use a monospaced
font.

Thanks for response

Your formula seems:
57 abc 8798
65 j 9872
71 a dc 98
87 a bc 573

But I prefer

57 abc 8798
65 j 9872
71 adc 98
87 abc 573

Thanks


Your formu
"Paul" none wrote in message
...
"ibo" wrote in message
...
Hi there

I have a cell with letters and numbers. Like below:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Is it possible to formula for substitute first two numbers on the left
and
other numbers on the right like this:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Thanks for advance.

Ibo


You can construct a formula to add spaces between the letters and the
second
group of numbers so that the whole string will be 11 characters long:
=LEFT(A1,FIND(" ",A1,4))&REPT(" ",11-LEN(A1))&RIGHT(A1,LEN(A1)-FIND("
",A1,4))
However, the second set of numbers will only line up if you use a
monospaced
font.

Thanks for response
Your formula seems:
57 abc 8798
65 j 9872
71 adc 98
87 abc 573

But I prefer
57 abc 8798
65 j 9872
71 adc 98
87 abc 573

Thanks



"Ilan Rencus" wrote in message
om...
Assuming you have one space between each group, you can use somthing like
this:
your data in A1 then (I broke the formula into several subformulas for
simplicity)
In B1 =FIND(" ",A1)
In C1 =MID(A1,B1+1,LEN(A1)-B1+1)
In D1 =FIND(" ",C1)
In E1 =RIGHT(C1,LEN(C1)-D1)
In F1 =LEFT(C1,D1-1)
In G1 =LEFT(A1,B1-1)
In H1 =E1&" "&F1&" "&G1

Ilan



"Paul" none wrote in message
...
"ibo" wrote in message
...
Hi there

I have a cell with letters and numbers. Like below:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Is it possible to formula for substitute first two numbers on the left
and
other numbers on the right like this:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Thanks for advance.

Ibo


You can construct a formula to add spaces between the letters and the
second
group of numbers so that the whole string will be 11 characters long:
=LEFT(A1,FIND(" ",A1,4))&REPT(" ",11-LEN(A1))&RIGHT(A1,LEN(A1)-FIND("
",A1,4))
However, the second set of numbers will only line up if you use a
monospaced
font.

Hi Ian
Ignore it my first reply,Your formula seems:

8798 abc 57
9872 j 65
98 adc 71
573 abc 87

I prefer:
57 abc 8798
65 j 9872
71 adc 98
87 ab 573


I prefer
"Ilan Rencus" wrote in message
om...
Assuming you have one space between each group, you can use somthing like
this:
your data in A1 then (I broke the formula into several subformulas for
simplicity)
In B1 =FIND(" ",A1)
In C1 =MID(A1,B1+1,LEN(A1)-B1+1)
In D1 =FIND(" ",C1)
In E1 =RIGHT(C1,LEN(C1)-D1)
In F1 =LEFT(C1,D1-1)
In G1 =LEFT(A1,B1-1)
In H1 =E1&" "&F1&" "&G1

Ilan



"Paul" none wrote in message
...
"ibo" wrote in message
...
Hi there

I have a cell with letters and numbers. Like below:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Is it possible to formula for substitute first two numbers on the left
and
other numbers on the right like this:

57 abc 8798
65 j 9872
71 adc 98
87 ab 573

Thanks for advance.

Ibo


You can construct a formula to add spaces between the letters and the
second
group of numbers so that the whole string will be 11 characters long:
=LEFT(A1,FIND(" ",A1,4))&REPT(" ",11-LEN(A1))&RIGHT(A1,LEN(A1)-FIND("
",A1,4))
However, the second set of numbers will only line up if you use a
monospaced
font.