Extracting multiple numbers from an excel cell having long description.

A

adityabhatt1989

New Member
Joined
Jun 12, 2014
Messages
9
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-


[TABLE="width: 561"]
<tbody>[TR]
[TD="class: xl1902, width: 561"]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

[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
adityabhatt1989 said:
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-


[TABLE="width: 561"]
<tbody>[TR]
[TD="class: xl1902, width: 561"]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

[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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)

[TABLE="width: 571"]
<tbody>[TR]
[TD]BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694[/TD]
[TD][/TD]
[TD]04.00[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Taisho said:
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.
Click to expand...
Hello @Taisho and @Tfox2791, Here are some more examples:

[TABLE="width: 561"]
<tbody>[TR]
[TD="class: xl1903, width: 561"]1. 9.56" OD X 2.63/18" ID HOLLOW STEEL BAR 4188 GRAD[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 561"]
<tbody>[TR]
[TD="class: xl1903, width: 561"]2. HFD BLNK 3.767X 4.20X5.65X 260 4740/48[/TD]
[/TR]
</tbody>[/TABLE]

3. [TABLE="width: 561"]
<tbody>[TR]
[TD="class: xl1903, width: 561"]TBRF 7.45 X 4.50 4560HE 90-105KSI YIELD

4. [TABLE="width: 561"]
<tbody>[TR]
[TD="class: xl1903, width: 561"]TUBE FD 07.890X04.670 ALLOY-GFD HT ES3.37163[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
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
 
Upvote 0
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:
Code: 
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.[TABLE="width: 747"]
<tbody>[TR]
[TD]BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694
[/TD]
[TD]04.00
[/TD]
[TD]02.25
[/TD]
[TD]26.50
[/TD]
[TD]1340
[/TD]
[TD]4.38694
[/TD]
[/TR]
[TR]
[TD]9.56" OD X 2.63/18" ID HOLLOW STEEL BAR 4188 GRAD
[/TD]
[TD]9.56
[/TD]
[TD]2.63
[/TD]
[TD]18
[/TD]
[TD]4188
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HFD BLNK 3.767X 4.20X5.65X 260 4740/48
[/TD]
[TD]3.767
[/TD]
[TD]4.20
[/TD]
[TD]5.65
[/TD]
[TD]260
[/TD]
[TD]4740
[/TD]
[/TR]
[TR]
[TD]TBRF 7.45 X 4.50 4560HE 90-105KSI YIELD
[/TD]
[TD]7.45
[/TD]
[TD]4.50
[/TD]
[TD]4560
[/TD]
[TD]90
[/TD]
[TD]105
[/TD]
[/TR]
[TR]
[TD]TUBE FD 07.890X04.670 ALLOY-GFD HT ES3.37163
[/TD]
[TD]07.890
[/TD]
[TD]04.670
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Taisho said:
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:
Code: 
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[TABLE="width: 747"]
<tbody>[TR]
[TD]BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694[/TD]
[TD]04.00[/TD]
[TD]02.25[/TD]
[TD]26.50[/TD]
[TD]1340[/TD]
[TD]4.38694[/TD]
[/TR]
[TR]
[TD]9.56" OD X 2.63/18" ID HOLLOW STEEL BAR 4188 GRAD[/TD]
[TD]9.56[/TD]
[TD]2.63[/TD]
[TD]18[/TD]
[TD]4188[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HFD BLNK 3.767X 4.20X5.65X 260 4740/48[/TD]
[TD]3.767[/TD]
[TD]4.20[/TD]
[TD]5.65[/TD]
[TD]260[/TD]
[TD]4740[/TD]
[/TR]
[TR]
[TD]TBRF 7.45 X 4.50 4560HE 90-105KSI YIELD[/TD]
[TD]7.45[/TD]
[TD]4.50[/TD]
[TD]4560[/TD]
[TD]90[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]TUBE FD 07.890X04.670 ALLOY-GFD HT ES3.37163[/TD]
[TD]07.890[/TD]
[TD]04.670[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Hello Taisho:

This seems interesting!

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

Thanks
Aditya
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Automate data export from multiple word tables into excel
Replies
8
Views
464
NdNoviceHlp
NdNoviceHlp
E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
1K
Sektor
Sektor
R
  • Solved
Extract a string of text from a cell
2 3
Replies
24
Views
1K
Akuini
Akuini
U
  • Question Question
Text Value from Textbox to Integer
Replies
1
Views
431
Kaper
Kaper
C
  • Question Question
Extracting Number String from a Text String Cell
Replies
9
Views
1K
Glenn Kennedy
G

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top