Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Extract Text From Alphanumeric String

1. Senior Member
Join Date
12th October 2006
Location
London
Posts
160

## Extract Text From Alphanumeric String

Hi,
I need to extract just the text from cells which contain both letters and numbers. There can be one or two letters, followed by one, two or three numbers. (For info, these are chemical elements and their masses).

So for instance,a cell could contain anything from the following B9, B11, S32, Ca44, Mo100, I129, Th226, U238 etc etc.

I know I've seen this done before, but I can't find it now.

It also has to be formula based, not using VBA.

Anyone remember how to do this please?

Thanks

Dave

Excel Video Tutorials / Excel Dashboards Reports

2. Banned
Join Date
18th September 2005
Location
Hampshire, UK
Posts
1,278

## Re: Extract Text From Alphanumeric String

Hi Dave

Here's one option:

=MID(A1,1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)

Richard

EDIT: More correctly:

=LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)
Last edited by RichardSchollar; July 16th, 2007 at 18:45.

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Extract Text From Alphanumeric String

See: Extract Numbers From Text String Custom Function.

or to sort, see Sort alphanumerics

4. Senior Member
Join Date
12th October 2006
Location
London
Posts
160

## Re: Extract Text From Alphanumeric String

Thanks Richard, that's perfect.

Thanks Dave, I'd found that UDF to extract numbers, and used it before, but this time I needed to extract the text.

Many thanks to everyone here.

Dave

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Extract Text From Alphanumeric String

Sorry, my bad I assumed you wanted the numbers.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno