Help extracting only numbers from a string of text and numbers

[LittleM]

Hello,

I need help finding a way to extract only the numbers from a string of text and numbers. I have a ton of rows that look like (spaces included):

at point X=632517.4350 Y=2774893.1403 Z=1540.0075 starting width 0.4000 ending width 0.8000

<tbody>
</tbody>

And ideally I would like a result that looks like something along the lines of :

632517.4350, 2774893.1403, 1540.0075, 0.4000, 0.8000

as I need to upload the data into another program that will read comma delineated files.

Any help is much appreciated!

Thanks

 

[pgc01]

Hi
Welcome to the board

Is your input always exactly like what you posted, meaning are the strings always "X=", "starting width", etc.?

 

[LittleM]

Yes, the text will always be the same but the numbers will be different.

 

[taurean]

You can do FIND and REPLACE

• FIND:at point X= REPLACE:Empty
• FIND:Y= REPLACE:", " without quotes
• FIND:Z= REPLACE:", " without quotes
• FIND:starting width REPLACE:", " without quotes
• FIND:ending width REPLACE:", " without quotes

 

[pgc01]

Another way is to replace the strings with commas using a formula.

Try:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"at point X=",""),"Y=",","),"Z=",","),"starting width ",","),"ending width ",",")

 

[JoeMo]

Yet another way is through VBA. Assuming your data are in column A starting in A1:

Sub GetNumbersOnly()
Dim lR As Long, vA As Variant, R As Range, S As Variant, sO As String
lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A1", "A" & lR)
R.Replace "?=", ""
vA = R.Value
For i = LBound(vA, 1) To UBound(vA, 1)
    S = Split(vA(i, 1), " ")
    For j = LBound(S) To UBound(S)
        If IsNumeric(S(j)) Then
            sO = sO & ", " & S(j)
        End If
    Next j
    R.Cells(i, 1).Value = Right(sO, Len(sO) - 2)
    sO = vbNullString
Next i
End Sub

 

[LittleM]

"=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"at point X=",""),"Y=",","),"Z=",","),"starting width ",","),"ending width ",",")"

This worked! Thank you so much, I appreciate it!

 

[taurean]

Assuming decimal point will always be there:

Public Sub GetNumsOnly()
Dim r As Range
For Each r In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    r.Offset(0, 1).Value = Join(Filter(Split(Replace(r.Value, "=", " "), " "), ".", True), ",")
Next
End Sub

 

[pgc01]

I see you got several working solutions. I'm glad your problem is solved!