Split numbers and text
 

Hi i have a spreadsheet which shows numbers and text:-
e.g
1FIC-2
16TIC-14/1
129IL-123B

I need to show the first number in one column, floowed by the rest in
another column.
e.g
1 FIC-2
16 TIC-14/1
129 IL-123B

Any help would be appreciated
Graham

Split numbers and text
 

Graham,

Lets assume your first number is in a1, put this in b1

=LOOKUP(6.022*10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Then this in C1
=RIGHT(A1,LEN(A1)-LEN((B1)))

Drag both down. Note the second formula is dependent on the first so if you
put the first in a cell other than b1 be sure to refer to that cell in the
second formula.

Mike



"Graham" wrote:

Hi i have a spreadsheet which shows numbers and text:-
e.g
1FIC-2
16TIC-14/1
129IL-123B

I need to show the first number in one column, floowed by the rest in
another column.
e.g
1 FIC-2
16 TIC-14/1
129 IL-123B

Any help would be appreciated
Graham

Split numbers and text
 

with slightly adjusted formulae posted yesterday by Teethless Mama:

numbers:
=LEFT(A1,MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT("65 :90"))),A1)),"",FIND
(CHAR(ROW(IND*IRECT("65:90"))),A1)))-1 )

"the rest":
=RIGHT(A1,LEN(A1)-MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT
("65:90"))),A1)),"",FIND(CHAR(ROW(IND*IRECT("65:90 "))),A1)))+1))

array-enter these formulae i.e. CTRL+SHIFT+ENTER

HIH


On 4 Lis, 10:08, Graham wrote:
Hi i have a spreadsheet which shows numbers and text:-
e.g
1FIC-2
16TIC-14/1
129IL-123B

I need to show the first number in one column, floowed by the rest in
another column.
e.g
1 * * *FIC-2
16 * *TIC-14/1
129 *IL-123B

Any help would be appreciated
Graham