View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default EXTRACT NUMBERS FROM TEXT STRING

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.
DataText to ColumnsDelimitedcheck 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