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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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
47,475
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
47,475
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,650
Members
412,335
Latest member
cinciri99
Top