Numbers and Text in the same cell - Need to Seperate

[bodazx]

This is a problem I have seen too many times,

I have a spreadsheet with a list of SKUs, of varying character length, formatted as a mix of numbers and text. Many of the SKUs in this column are simple #s (eg 555002), but others have a letter or two added on to the end to indicate a slight variation (eg 555002c). I need to develop a sheet that vertically lists the base SKU #s, with the letter variants horizontally listed in columns to the right
Example:
555002 c
555003 d kz
555004
32105 vsc

The problem is that the SKUs are not all of the same length, nor are there a fixed number of letters on the end of each.

How can I seperate the numbers from the letters, when the cell is formatted all as "Text".

 

[Special-K99]

Assuming data in A1

in B1
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))
Array formula, use Ctrl-Shift-Enter

in C1
=SUBSTITUTE(A1,B1,"")
copy down columns

 

[JoeMo]

This is a problem I have seen too many times,

I have a spreadsheet with a list of SKUs, of varying character length, formatted as a mix of numbers and text. Many of the SKUs in this column are simple #s (eg 555002), but others have a letter or two added on to the end to indicate a slight variation (eg 555002c). I need to develop a sheet that vertically lists the base SKU #s, with the letter variants horizontally listed in columns to the right
Example:
555002 c
555003 d kz
555004
32105 vsc

The problem is that the SKUs are not all of the same length, nor are there a fixed number of letters on the end of each.

How can I seperate the numbers from the letters, when the cell is formatted all as "Text".

Assuming any letters are always after the numbers try this array formula (confirm with shift+control+enter - not just enter):

=LEFT(A1,SUM(IF(ISNUMBER(VALUE(LEFT(A1,ROW($1:$99)))),1,0)))

This will return the number as text even if it's a standalone number.
If you want to return it as a number, wrap the formula above in the VALUE function and enter with shift+control+enter.

 

[bodazx]

Can you please provide an explanation of how this works? I still don't quite understand array formulas.

 

[dsaffo]

If there is always a space between the letters and numbers, you can try this to avoid an array formula:

=IFERROR(MID(A1,1,FIND(" ",A1)-1),A1)

IFERROR might be Excel 2007 or later. If you ar eusing an earlier version I think you need to use:

=IF(ISERROR(FIND(" ",A1)),A1,MID(A1,1,FIND(" ",A1)-1))

 

[bodazx]

I also need to pull the letter variations from data in another worksheet. For example, in sheet1, there may be 555003a, 55503c, 55503k, and in sheet2, I need to have (in seperate columns):
555003 a c k