EXTRACT NUMBERS FROM TEXT STRING

Hey Guys,
I have a text string with numbers that I need to separate into adjoining
columns.
Example:
HBFO(144)(144)(36)(24)(24)[6] This is a bid item on a government project.
Each number enclosed in parenthesis represents a fiber optic cable. I need
to extract the different numbers and place each of them in a separate column
for calculating the bid,,i.e
A B C D
E F G
HBFO(144)(144)(36)(24)(24)[6] 144 144 36 24 24 6

Your help will be greatly appreciated
Private email, OK [email protected]

Hi

One way. Firstly make a COPY of your data.
Ensure you have blank columns B:G, with your data in column A.
Mark the block of data in column A.
Data>Text to Columns>Delimited>check Other and put ( as the other separator
>Finish
Mark column F and repeat process, but change delimiter to [
Press Ctrl+H to bring up Find and Replace, in the Find pane type ( and leave
Replace blank, pres Replace All.
Repeat with [ in Find Pane.

Regards

Roger Govier


fiber_doc wrote:
> Hey Guys,
> I have a text string with numbers that I need to separate into adjoining
> columns.
> Example:
> HBFO(144)(144)(36)(24)(24)[6] This is a bid item on a government project.
> Each number enclosed in parenthesis represents a fiber optic cable. I need
> to extract the different numbers and place each of them in a separate column
> for calculating the bid,,i.e
> A B C D
> E F G
> HBFO(144)(144)(36)(24)(24)[6] 144 144 36 24 24 6
>
> Your help will be greatly appreciated
> Private email, OK [email protected]
>

I'd use a find/replace to delete each ) and ], and to change [ to (. Then
use Data > Text to Columns, indicating that the columns are delimited by (.

"fiber_doc" wrote:

> Hey Guys,
> I have a text string with numbers that I need to separate into adjoining
> columns.
> Example:
> HBFO(144)(144)(36)(24)(24)[6] This is a bid item on a government project.
> Each number enclosed in parenthesis represents a fiber optic cable. I need
> to extract the different numbers and place each of them in a separate column
> for calculating the bid,,i.e
> A B C D
> E F G
> HBFO(144)(144)(36)(24)(24)[6] 144 144 36 24 24 6
>
> Your help will be greatly appreciated
> Private email, OK [email protected]
>

Thanks for your reply. It worked but with only 1 problem. I need the
numbers enclosed in brackets "[ ]" to all line up in the same column. They
are the number of service drops at that location and a lot of calculations
depend on the info in the "Drops" column. Is there anyway to automate this
process with VBA or a built-in function?

I recently purchased John Walkenbach's "Excel 2003 Formulas" and "Power
Programming with VBA" but do not yet understand what I'm doing, or more
correctly, what the statements in VBA are doing.

"Roger Govier" wrote:

> Hi
>
> One way. Firstly make a COPY of your data.
> Ensure you have blank columns B:G, with your data in column A.
> Mark the block of data in column A.
> Data>Text to Columns>Delimited>check Other and put ( as the other separator
> >Finish
> Mark column F and repeat process, but change delimiter to [
> Press Ctrl+H to bring up Find and Replace, in the Find pane type ( and leave
> Replace blank, pres Replace All.
> Repeat with [ in Find Pane.
>
> Regards
>
> Roger Govier
>
>
> fiber_doc wrote:
> > Hey Guys,
> > I have a text string with numbers that I need to separate into adjoining
> > columns.
> > Example:
> > HBFO(144)(144)(36)(24)(24)[6] This is a bid item on a government project.
> > Each number enclosed in parenthesis represents a fiber optic cable. I need
> > to extract the different numbers and place each of them in a separate column
> > for calculating the bid,,i.e
> > A B C D
> > E F G
> > HBFO(144)(144)(36)(24)(24)[6] 144 144 36 24 24 6
> >
> > Your help will be greatly appreciated
> > Private email, OK [email protected]
> >
>

Hi

Sorry for the delay in response, but I have been off-line for a few days.
If you are saying you want to retain the [ ] around the data for the last
column, one way would be to do a Find and Replace before you start.

Press Ctrl+H to bring up Find and Replace, in the Find pane type [ and in
the Replace pane type ([, Replace All.
Now, the Data>Text to columns only needs to be done with ( as the delimiter,
and, you final Find and Replace only needs to be done to remove the ).

If you want to Automate it, turn on the Macro Recorder before going through
the various steps.
Tools>Macro>Record carry out the different stages of the task, then switch
off the recorder at the end by pressing the "X" on the little bar that
appears on the screen when you turn it on.

When you want to re-use it, just Tools>Macro>Macros and select the Macro.
Alternatively, when you have the Macro selected, go to Options and give it a
shortcut key like Ctrl+q. Then whenever you need to run it, just press Ctrl+q.

I would do a few practice runs first to familiarise yourself with the steps,
before you try recording.


Regards

Roger Govier


fiber_doc wrote:
> Thanks for your reply. It worked but with only 1 problem. I need the
> numbers enclosed in brackets "[ ]" to all line up in the same column. They
> are the number of service drops at that location and a lot of calculations
> depend on the info in the "Drops" column. Is there anyway to automate this
> process with VBA or a built-in function?
>
> I recently purchased John Walkenbach's "Excel 2003 Formulas" and "Power
> Programming with VBA" but do not yet understand what I'm doing, or more
> correctly, what the statements in VBA are doing.
>
> "Roger Govier" wrote:
>
>
>>Hi
>>
>>One way. Firstly make a COPY of your data.
>>Ensure you have blank columns B:G, with your data in column A.
>>Mark the block of data in column A.
>>Data>Text to Columns>Delimited>check Other and put ( as the other separator
>> >Finish
>>Mark column F and repeat process, but change delimiter to [
>>Press Ctrl+H to bring up Find and Replace, in the Find pane type ( and leave
>>Replace blank, pres Replace All.
>>Repeat with [ in Find Pane.
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>fiber_doc wrote:
>>
>>>Hey Guys,
>>>I have a text string with numbers that I need to separate into adjoining
>>>columns.
>>>Example:
>>>HBFO(144)(144)(36)(24)(24)[6] This is a bid item on a government project.
>>>Each number enclosed in parenthesis represents a fiber optic cable. I need
>>>to extract the different numbers and place each of them in a separate column
>>>for calculating the bid,,i.e
>>> A B C D
>>> E F G
>>>HBFO(144)(144)(36)(24)(24)[6] 144 144 36 24 24 6
>>>
>>>Your help will be greatly appreciated
>>>Private email, OK [email protected]
>>>
>>