Extracting numbers from text strings

fiber_doc

New Member
Joined
Nov 8, 2005
Messages
2
Hey Guys,
I have the following info in a spreadsheet that represents bid items. These entries vary in length and I wish to separate them into their respective columns for further analysis:

HBFO(144)(144)(36)(24)(24)[2]
HBFO(144)(144)(12)[5]
HBFO(288)(288)[7]
HBFO(288)(288)(72)(48)(36)[13]
HBFO(12)[6]

All of the entries with "[ ]" need to be in the same column for further calculations, as these are crucial for the bidding process.

Private emails ok, ken@kiacomm.us
Any help here will be greatly appreciated
-ken
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
If I got you right, then

assuming your text is in A1,

In B1, =LEFT(A1,FIND("[",A1)-1)

In C1, =MID(A1,FIND("[",A1)+1,LEN(A1)-FIND("[",A1)-1)

copy both down.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,433
Office Version
  1. 365
Platform
  1. Windows
fiber_doc

Welcome to the Mr Excel board!

Here is another interpretation of your question. If NBVC and I have both mis-interpreted your question, then it probably means that you need to re-state it more clearly and perhaps give an example of the result you expect.

The formula in B2 (copied across and down) is:
=IF(RIGHT($A2,LEN(B$1))=B$1,SUBSTITUTE($A2,B$1,""),"")
Mr Excel.xls
ABCDEFGH
1[1][2][3][4][5][6][7]
2HBFO(144)(144)(36)(24)(24)[2] HBFO(144)(144)(36)(24)(24)     
3HBFO(144)(144)(12)[5]    HBFO(144)(144)(12)  
4HBFO(288)(288)[7]      HBFO(288)(288)
5HBFO(288)(288)(72)(48)(36)[13]       
6HBFO(12)[6]     HBFO(12) 
7
Group Bids
 

fiber_doc

New Member
Joined
Nov 8, 2005
Messages
2
Thanks for your replies. I tried to use Colo's add-in and apparently I'm not doing something right. Anyway the info you requested is below. I'm trying to extract the numbers from a string of text and separate them into columns.
As you can see by the example below, the text string varies in length, but in each row I wish to have the numbers in "[ ]" aligned in the same column.

Example:
FROM THIS ->>>>>>>>>>>>-TO THIS
HBFO(144)(144)(24)[3] 144 144 24 3
HBFO(144)(144)(72)(36)[4] 144 144 72 36 4
HBFO(36)[6] 36 6

Hope this helps explain things a bit better.
Please email me and I will send you a copy of the sheet I'm trying to work through. ken@kiacomm.us[/img]
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,433
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

fiber_doc

Keep trying with the HtmlMaker as I am still not exactly sure of the layout you want. Is this it?

Formula in B2 (copied down) is:
=SUBSTITUTE(MID(A2,1+FIND("(",A2),FIND("[",A2)-FIND("(",A2)-2),")("," ")

C2 (copied down) is:
=MID(A2,1+FIND("[",A2),LEN(A2)-FIND("[",A2)-1)
Mr Excel.xls
ABCD
1
2HBFO(144)(144)(36)(24)(24)[2]144 144 36 24 242
3HBFO(144)(144)(12)[5]144 144 125
4HBFO(288)(288)[7]288 2887
5HBFO(288)(288)(72)(48)(36)[13]288 288 72 48 3613
6HBFO(12)[6]126
Group Bids (2)


Or do you want column B numbers in individul columns? If so, would it be OK to move the numbers I currently have in column C to column B and put the others in columns C, D, E etc. The difficulty is that there is not a consistent number of numbers in the middle of your items, so how many columns to leave before the final [] number?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
vba solution
Code:
Sub test()
Dim x, i As Long, r As Range, y()
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
    If InStr(r, "[") > 0 Then
        x = Replace(Replace(Replace(r, "[", "("), "]", ""), ")", "")
        ReDim Preserve y(n)
        y(n) = Split(x, "(")
        m = Application.Max(m, UBound(y(n)))
        x = Empty: n = n + 1
    End If
Next
For i = 0 To UBound(y)
    For ii = 1 To UBound(y(i)) - 1
        Cells(i + 1, ii + 1) = y(i)(ii)
    Next
    Cells(i + 1, m + 1) = y(i)(UBound(y(i)))
Next
Erase y
End Sub
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
Maybe this layout?
VBA timer trial.xls
ABCDEF
1[ ]
2
3HBFO(144)(144)(24)[3] 144 144 24 3144144243
4HBFO(144)(144)(72)(36)[4] 144 144 72 36 414414472364
5HBFO(36)[6] 36 6366
Sheet2
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,549
Messages
5,838,044
Members
430,527
Latest member
MyFace2

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
Top