extract numbers from a string

[Dave]

Hello,

We import strings of numbers into a spreadsheet looking like
this:-

12Rev999,nextrev456
124Rev8888,nextrev1234

In practice the numbers can be any length from 1 to 4 numbers and there are
always three groups. I need to extract these numbers which are in Column A to
Column C
nearly forgot to mention the numbers in this format are text and I need them
as numbers.

Hope you can help.

D

 

[Lars-Åke Aspelin]

Hello,

We import strings of numbers into a spreadsheet looking like
this:-

12Rev999,nextrev456
124Rev8888,nextrev1234

In practice the numbers can be any length from 1 to 4 numbers and there are
always three groups. I need to extract these numbers which are in Column A to
Column C
nearly forgot to mention the numbers in this format are text and I need them
as numbers.

Hope you can help.

D

Assuming your strings are in column D you may try the following
formulas in cells A1, B1, and C1 respectivey:

=LEFT(D1,FIND("Rev",D1)-1)+0

=MID(D1,FIND("Rev",D1)+3,FIND("nextrev",D1)-FIND("Rev",D1)-4)+0

=RIGHT(D1,LEN(D1)-FIND("nextrev",D1)-6)+0

Hope this helps / Lars-Åke

 

[Duke Carey]

if you ALWAYS have "Rev" and ",nextrev" as the only alpha characters, then

=SUBSTITUTE(SUBSTITUTE(A1,"Rev",""),",nextrev","")*1

will work

 

[Otto Moehrbach]

Dave
If all else fails, this little macro will do it for you. I assumed your
data is in Column A starting in A1. This macro displays a message box
showing you the number for each entry in Column A. HTH Otto
Sub GetNums()
Dim rColA As Range
Dim i As Range
Dim c As Long
Dim Nums As Long
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In rColA
c = 1
Nums = 0
For c = 1 To Len(i)
If IsNumeric(Mid(i, c, 1)) Then
Nums = --(Nums & Mid(i, c, 1))
End If
Next c
MsgBox Nums
Next i
End Sub