Extracting numbers from text strings

[fiber_doc]

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

 

[NBVC]

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]

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
	A	B	C	D	E	F	G	H
1		[1]	[2]	[3]	[4]	[5]	[6]	[7]
2	HBFO(144)(144)(36)(24)(24)[2]		HBFO(144)(144)(36)(24)(24)
3	HBFO(144)(144)(12)[5]					HBFO(144)(144)(12)
4	HBFO(288)(288)[7]							HBFO(288)(288)
5	HBFO(288)(288)(72)(48)(36)[13]
6	HBFO(12)[6]						HBFO(12)
7
Group Bids

 

[fiber_doc]

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]

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
	A	B	C	D
1
2	HBFO(144)(144)(36)(24)(24)[2]	144 144 36 24 24	2
3	HBFO(144)(144)(12)[5]	144 144 12	5
4	HBFO(288)(288)[7]	288 288	7
5	HBFO(288)(288)(72)(48)(36)[13]	288 288 72 48 36	13
6	HBFO(12)[6]	12	6
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]

Hi
vba solution

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]

Maybe this layout?

VBA timer trial.xls
	A	B	C	D	E	F
1						[ ]
2
3	HBFO(144)(144)(24)[3] 144 144 24 3	144	144	24		3
4	HBFO(144)(144)(72)(36)[4] 144 144 72 36 4	144	144	72	36	4
5	HBFO(36)[6] 36 6	36				6
Sheet2