Separating information from a single cell

[rofl]

I have a column with the information like the following: 20016ConsumerHD

I'm trying to separate the number (20016) from the name (ConsumerHD), is there any way to do this? The number is always 5 characters long, maybe pull the first 5 characters?

[snasui]

I have a column with the information like the following: 20016ConsumerHD

I'm trying to separate the number (20016) from the name (ConsumerHD), is there any way to do this? The number is always 5 characters long, maybe pull the first 5 characters?

Try this:

Suppose your data is in A1

B1

=Left(A1,5)

Enter

Hope this helps.

[NBVC]

Try:

=Left(A1,5)+0 to get the number

and =Mid(A1,6,255) to get the text

[rofl]

Wow, thanks guys! I wish I knew what to search for in the excel help so I can find these commands myself... I need a help file for the help file.

[wmorrison49]

Text To Columns is always a helpful item for me. You can find it under the Data option in the main toolbar. This gives a more visual way of doing the same thing.

[sharpmel]

This function will not work for me (I am using a date). I referenced a cell with a date AND a time and I only need the date (the first 12 characters). That formula is giving me a weird output. =LEFT('Qualtrics Output'!I3,12)+0 changes nothing and =LEFT('Qualtrics Output'!I3,12) gives me 41239.525601. The referenced cell is 11/26/2012 12:36.

[vlady]

its a matter of formatting.