Split numbers and text

  • Thread starter Thread starter Graham
  • Start date Start date
G

Graham

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
 
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
 
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
 
Back
Top