Split text and numbers into different cells

**frutz** · 06-11-2009, 03:53 AM

Hi there,

I have data in a single column that looks sth like this:

CTE_DIV_96657
TOMMY_DIV_94434
PHILLIP CHUA - DIV 95343

As you can see, the text and numbers are seperated sometimes by spaces and sometimes by _ or -. I need to seperate this data into 3 seperate columns

i.e. CTE | DIV | 96657

Then, I need to highlight duplicates in the last column (96657,etc) without deleting the duplicates as would happen if I used Advanced Filter > Unique records only

I have tried using the formula =RIGHT(A1,5), but I have no way of checking for duplicates by hightlighting repeated entries (I need to conditional format to detect duplicate numbers )

Can anyone please help?

**teodormircea** · 06-11-2009, 04:26 AM

Here by an example by formulas, see attached excel file.

**frutz** · 06-12-2009, 03:13 AM

I see, thanks!

Is there any way a general formula can be written to seperate text and numbers? Or does it all depend on the

i. position of the characters
ii. number of characters (text or nos)?

thanks for the fast reply!

**Richard Schollar** · 06-12-2009, 04:52 AM

There are worksheet formulas that can extract the numeric digits from a string (eg see Ron Coderre's formula in the following:

http://www.excelforum.com/excel-work...c-strings.html

), but text can be a problem. If you are open to using UDFs (ie macro code written as a function that you can also use in your worksheet) then this is pretty straightforward:

Please Login or Register  to view this content.

You would then use this in your wworksheet like:

=ExtractCharacters(A1,1)

If you wanted to extract digits from the A1 cell, or

=ExtractCharacters(A1,0)

If you wanted characters other than digits.

Richard