Extracting multiple numbers from an excel cell having long description.

[adityabhatt1989]

Hello Everyone:

I am new to MrExcel.com forums and this is my first post here.

I need to extract numbers from excel cells. For eg-

BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694 ^^^ This is one of the cells. I need to pull out 04.00, 2.25, 26.50, 1340 into different cells from one given cell. I have tried using =MID() function, but I need to make changes into that formula everytime the position of first numeric letter changes while I go along a column. And I need to make changes into it as I go along the row. It is taking me a lot of time in extracting numbers from one cell into different cells and I am afraid that I will spend the rest of my life getting this stuff done. Any help with this task is hugely appreciated and acknowledged. PS- There might already be a discussion thread on this. Posting that here would he helpful too. Best Aditya Bhatt

<tbody>
</tbody>

 

[Tfox2791]

Hello Everyone:

I am new to MrExcel.com forums and this is my first post here.

I need to extract numbers from excel cells. For eg-

BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694 ^^^ This is one of the cells. I need to pull out 04.00, 2.25, 26.50, 1340 into different cells from one given cell. I have tried using =MID() function, but I need to make changes into that formula everytime the position of first numeric letter changes while I go along a column. And I need to make changes into it as I go along the row. It is taking me a lot of time in extracting numbers from one cell into different cells and I am afraid that I will spend the rest of my life getting this stuff done. Any help with this task is hugely appreciated and acknowledged. PS- There might already be a discussion thread on this. Posting that here would he helpful too. Best Aditya Bhatt

<tbody>
</tbody>

Can you provide a screenshot? If the first number is always 0 this will help with the starting position.

=MID(A3,FIND(0,A3),5)

BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694

04.00

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

 

[Taisho]

Hello, I would like you to give some more examples, the more the better, if we can see some pattern it's not gonna be hard to code it.

 

[Trouttrap2]

You could try using the built in tool "Text to Columns". Using Delimiters of Tab, Space, Other: Uppercase X.That worked for me using your example. Then just delete the other columns you don't want.

 

[adityabhatt1989]

Hello, I would like you to give some more examples, the more the better, if we can see some pattern it's not gonna be hard to code it.

Hello @Taisho and @Tfox2791, Here are some more examples:

1. 9.56" OD X 2.63/18" ID HOLLOW STEEL BAR 4188 GRAD

<tbody>
</tbody>

2. HFD BLNK 3.767X 4.20X5.65X 260 4740/48

<tbody>
</tbody>

3.

TBRF 7.45 X 4.50 4560HE 90-105KSI YIELD 4. TUBE FD 07.890X04.670 ALLOY-GFD HT ES3.37163 <tbody> </tbody>

<tbody>
</tbody>

As you can see, there are multiple numbers per cell. I only need first three if getting every number from this is difficult. eg- in 1st example above I will only need 9.56", 2.63 and 18.

If there is one number after another, they will genreally be seperated by "X". Numbers might be seoerated from character/ text by space or no space.


Thanks everyone
Aditya

 

[Taisho]

which values do you need out of additional examples 2-4?

 

[adityabhatt1989]

which values do you need out of additional examples 2-4?

I need 7.45, 4.50, and 90 in 3rd example
and 07.890 and 04.670 in example 4th.

 

[Taisho]

I have problems with not displaying "4560" in 3rd additional example. I may try to solve it later. Here is what I have at this moment:

Option Explicit
Sub hehe()
Dim i As Integer, j As Integer, numstart As Integer, numend As Integer, k As Integer
Dim toextract As String, char As String, sheetname As String
Dim thelastrow As Long, iRow As Long
Dim v As Variant
sheetname = "Arkusz1"
thelastrow = ThisWorkbook.Sheets(sheetname).Cells.Find(What:="*", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Row
        
v = ThisWorkbook.Sheets(sheetname).Range("a1:a" & thelastrow)
For iRow = LBound(v) To UBound(v)
k = 0
    toextract = v(iRow, 1)
    
    For i = 1 To Len(toextract)
        char = Mid(toextract, i, 1)
        If IsNumeric(char) = True Then
        
        numstart = i
        
            For j = i To Len(toextract)
                char = Mid(toextract, j, 1)
        
                If IsNumeric(char) = False And char <> "." Then
                    numend = j - 1
                    k = k + 1
                    Cells(iRow, k + 1) = Mid(toextract, numstart, numend - numstart + 1)
                    i = numend
                    Exit For
                End If
                
            Next j
            
        End If
    Next i
    
Next iRow
End Sub

The cells are from cell A1 onward. Remember to change sheet name for the one you use and to change cell format to text.

BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694	04.00	02.25	26.50	1340	4.38694
9.56" OD X 2.63/18" ID HOLLOW STEEL BAR 4188 GRAD	9.56	2.63	18	4188
HFD BLNK 3.767X 4.20X5.65X 260 4740/48	3.767	4.20	5.65	260	4740
TBRF 7.45 X 4.50 4560HE 90-105KSI YIELD	7.45	4.50	4560	90	105
TUBE FD 07.890X04.670 ALLOY-GFD HT ES3.37163	07.890	04.670

<tbody>
</tbody>

 

[adityabhatt1989]

I have problems with not displaying "4560" in 3rd additional example. I may try to solve it later. Here is what I have at this moment:

Option Explicit
Sub hehe()
Dim i As Integer, j As Integer, numstart As Integer, numend As Integer, k As Integer
Dim toextract As String, char As String, sheetname As String
Dim thelastrow As Long, iRow As Long
Dim v As Variant
sheetname = "Arkusz1"
thelastrow = ThisWorkbook.Sheets(sheetname).Cells.Find(What:="*", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Row
        
v = ThisWorkbook.Sheets(sheetname).Range("a1:a" & thelastrow)
For iRow = LBound(v) To UBound(v)
k = 0
    toextract = v(iRow, 1)
    
    For i = 1 To Len(toextract)
        char = Mid(toextract, i, 1)
        If IsNumeric(char) = True Then
        
        numstart = i
        
            For j = i To Len(toextract)
                char = Mid(toextract, j, 1)
        
                If IsNumeric(char) = False And char <> "." Then
                    numend = j - 1
                    k = k + 1
                    Cells(iRow, k + 1) = Mid(toextract, numstart, numend - numstart + 1)
                    i = numend
                    Exit For
                End If
                
            Next j
            
        End If
    Next i
    
Next iRow
End Sub

The cells are from cell A1 onward

BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694	04.00	02.25	26.50	1340	4.38694
9.56" OD X 2.63/18" ID HOLLOW STEEL BAR 4188 GRAD	9.56	2.63	18	4188
HFD BLNK 3.767X 4.20X5.65X 260 4740/48	3.767	4.20	5.65	260	4740
TBRF 7.45 X 4.50 4560HE 90-105KSI YIELD	7.45	4.50	4560	90	105
TUBE FD 07.890X04.670 ALLOY-GFD HT ES3.37163	07.890	04.670

<tbody>
</tbody>

Hello Taisho:

This seems interesting!

Forgive me for my ignorance but where do I have to go and run this code?

Thanks
Aditya

 

[adityabhatt1989]

Its OK to have or not have 4560 in the output. This seems great!