split numbers from text in cell

**nicko54** · 03-26-2009, 06:46 AM

Hi all,

I need to split the numbers from the text in a list of cells, to be in two columns

i.e. 'mark 2341' to split out to 'mark' in one colum and '2341' in the other.

does anyone know a quick formula?

many thanks,

Nicko

**arthurbr** · 03-26-2009, 06:50 AM

Hi
if your format is always the same ( string space numeric), you can use Data - Text to column - Delimited and use the space as delimiter

**mudraker** · 03-26-2009, 06:51 AM

Assuming they all have a space between the text and the numbers :-
Select column or data
Data menu
Text To Columns
Delimited
Tick Space Checkbox - Remove tick from all others
Finish buton or Next button to set Column Data Format

Ensure you have blank columns to right of data

**nicko54** · 03-26-2009, 07:20 AM

Brilliant! thanks worked a charm!

**mudraker** · 03-26-2009, 07:24 AM

nicko54

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save