Move numbers contained within a string

[Smithy]

Folks,

Hello all, newbie here so please be patient. I have entries in approx 6500 cells which contain a string that is a mixture of text and numbers. What I need to do is to move the number value of the cell to the adjoing cell on the right - it sounds fairly simple deosn't it? Why have I no hair then??

Not all strings have a number value, and if that's the case I just want the cell to remain blank. Those cells that have a numeric value always have it at the end of the string.

Below is a brief example:

This is what I've got:
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Consult/Exam 1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Consult/Exam 2</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Consult/Exam 3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Reception General </TD><TD></TD></TR></TBODY></TABLE>

This is what I'd like please:
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Consult/Exam </TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Consult/Exam </TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Consult/Exam </TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Reception General </TD></TR></TBODY></TABLE>

I've tried 'text to columns', that hasn't worked and all strings are of varing length.

Any help would be greatly appreciated

 

[Ed in Aus]

have you tried in b1 "=right(a1,1)" copy down

that will get the last character into column b

then to remove last character from a

in c1 "=left(a1,len(a1)-1"

copy paste values then delete column "A"

 

[Aladin Akyurek]

Try...

B1:

=TRIM(SUBSTITUTE(A1,C1,""))

C1:

=REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"")

 

[Smithy]

Try...

B1:

=TRIM(SUBSTITUTE(A1,C1,""))

C1:

=REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"")

Aladin - mate thanks very much. That worked a treat. Watch this space though, I have a feeling I'll be back shortly.

Thanks again,

S