Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Extract Separate Numbers From Letters

1. Established Member
Join Date
7th January 2005
Posts
451

## Extract Separate Numbers From Letters

I've found several posts but none seem to peform this varying function:
EX12345678....Result in Col B: "EX" and Result in Col C: "12345678"
RTZZ4567.......Result in Col B: "RTZZ" and Result in Col C: "4567"

The problem with the formulas I've got specifically define - pulling let's say LEFT, 2 characters.....when, I may need it to pull 2 or 3 or 4.

I found something that's smart enough to look for ONLY ALPHA and strip those out and place them into one column.
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

* I need something that's smart enough to look for ONLY NUMERIC. no matter how long the string is...and place those in Column C (like I mention in the example at the top).

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Extract Separate Numbers From Letters

Hi

Why not simply

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

Wigi

3. Established Member
Join Date
7th January 2005
Posts
451

## Re: Extract Separate Numbers From Letters

It almost works! Well, it does unless there's any odd characters at the end of the string such as this......
XKR33333ZZ 33333ZZ

Whereas, the formula used to extract alpha/text doesnt care whether there's characters mixed or not.....it simply stripped out ALL alphas no matter where they were placed within the string.

Is there a way to change the formula you posted to: look throughout the whole string and pull both the XKR and the ZZ (any existing alpha if applicable) and provide the result of:

Example:..............Result...
XKR33333ZZ.........XKRZZ

Ultimately, I'm trying to get this end result:
All alpha in one column and all numeric in the next column:

Example:..............Column A Result....Column B Result
XKR33333ZZ.........XKRZZ.................33333............

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Extract Separate Numbers From Letters

Looks a clear case for using VBA to me.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Extract Separate Numbers From Letters

Try these two functions.
VB:
```Function GetChars(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then Goto GoExit
For i = 1 To Len(target.Value)
If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetChars = MyStr
End Function

Function GetNums(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then Goto GoExit
For i = 1 To Len(target.Value)
If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetNums = MyStr
End Function

```
For a value in cell A1, in B1 enter
=GetChars(A1)
and in C1 enter
=GetNums(A1)

EDIT: Note that the GetChars function retains spaces included in the original text. To easily remove leading and trailing spaces change the line of code
VB:
```GetChars = MyStr

```
to
VB:
```GetChars = Trim(MyStr)

```
Removing internal spaces requires more code.
Last edited by thomach; February 1st, 2007 at 03:17.

6. Established Member
Join Date
7th January 2005
Posts
451

## Re: Extract Separate Numbers From Letters

The VBA option works beautifully!!! THANK YOU!!

If there's a way to achieve it with a single formula/function/array; (meaning, 1 formula for the alpha and a different formula to use in the numeric column) .....I'd love to have that option as well -- to be able to give it to co-workers that need to clean up parts lists quickly and have no familiarity with VBA or add-ins.

Chris

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Extract Separate Numbers From Letters

I'm afraid I'm limited to a VBA answer on this one. But for what it's worth (I had to play some more), this version of the GetChars function eliminates all spaces and any formatting characters that might have been included in teh string (e.g., line ejects).
VB:
```Function GetCharsNoSpaces(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then Goto GoExit
For i = 1 To Len(target.Value)
If Not IsNumeric(Mid(target, i, 1)) And Asc(Mid(target, i, 1)) <> 32 Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetCharsNoSpaces = Trim(WorksheetFunction.Clean(MyStr))
End Function

```

8. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

## Re: Extract Separate Numbers From Letters

Assuming that A2 contains the text string, and numbers occur together, maybe...

B2:

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

C2:

=SUBSTITUTE(A2,B2,"")

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

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