# Extracting numbers from text strings

#### fiber_doc

##### New Member
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### NBVC

##### Well-known Member
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
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
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
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
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
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

Replies
18
Views
239
Replies
3
Views
268
Replies
15
Views
740
Replies
10
Views
360
Replies
11
Views
584

1,195,993
Messages
6,012,745
Members
441,724
Latest member
Aalbid

### 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.

### Which adblocker are you using?

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

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