I have some data that came in a text files. It is hard to split the fields
because there is no even breaking point, not comma delimited or anything.
The text contains company names and bid number. The bid number always start
with B0?-???. I would like separate the company name and the bid number in
two columns.

Sample : A & F INDUSTRIES B06-171 AW
Breakdown: A & F INDUSTRIES (company name ) B06-171 AW
is the bid number.

Sample: ADMIRALITE MARINE & STRUCTURE B07-118 SL
Breakdown: ADMIRALITE MARINE & STRUCTURE (company name) B07-118 SL (bid
number). How can I extract or separte the data is in one column. I need it
in 2 columns. Right now the file is in .txt.

Thanks for any Help

On Sat, 26 May 2007 05:00:02 -0700, klafert
wrote:

I have some data that came in a text files. It is hard to split the fields
because there is no even breaking point, not comma delimited or anything.
The text contains company names and bid number. The bid number always start
with B0?-???. I would like separate the company name and the bid number in
two columns.

Sample : A & F INDUSTRIES B06-171 AW
Breakdown: A & F INDUSTRIES (company name ) B06-171 AW
is the bid number.

Sample: ADMIRALITE MARINE & STRUCTURE B07-118 SL
Breakdown: ADMIRALITE MARINE & STRUCTURE (company name) B07-118 SL (bid
number). How can I extract or separte the data is in one column. I need it
in 2 columns. Right now the file is in .txt.

Thanks for any Help



B1: =LEFT(A1,SEARCH("B0?-??? ",A1)-2)
C1: =MID(A1,SEARCH("B0?-???",A1),255)

Results of above formulas:

A1: A & F INDUSTRIES B06-171 AW
B1: A & F INDUSTRIES
C1: B06-171 AW

--------------------------------

A1: ADMIRALITE MARINE & STRUCTURE B07-118 SL
B1: ADMIRALITE MARINE & STRUCTURE
C1: B07-118 SL

--ron

This worked for the most part, but for some reason there are some that it
gave me a error on the company name but gave me the bid # or some didnt give
me anything. The error is #VALUE!. I tried to look up the command but I
don't know what the function is called. Can you explain what the formula
means or ... I know while some didnt because they only had company names and
not the bid number but that is fine I can copy those over to the next
column!! But for the most part this worked great!!

Thanks Mary


"Ron Rosenfeld" wrote:

On Sat, 26 May 2007 05:00:02 -0700, klafert
wrote:

I have some data that came in a text files. It is hard to split the fields
because there is no even breaking point, not comma delimited or anything.
The text contains company names and bid number. The bid number always start
with B0?-???. I would like separate the company name and the bid number in
two columns.

Sample : A & F INDUSTRIES B06-171 AW
Breakdown: A & F INDUSTRIES (company name ) B06-171 AW
is the bid number.

Sample: ADMIRALITE MARINE & STRUCTURE B07-118 SL
Breakdown: ADMIRALITE MARINE & STRUCTURE (company name) B07-118 SL (bid
number). How can I extract or separte the data is in one column. I need it
in 2 columns. Right now the file is in .txt.

Thanks for any Help



B1: =LEFT(A1,SEARCH("B0?-??? ",A1)-2)
C1: =MID(A1,SEARCH("B0?-???",A1),255)

Results of above formulas:

A1: A & F INDUSTRIES B06-171 AW
B1: A & F INDUSTRIES
C1: B06-171 AW

--------------------------------

A1: ADMIRALITE MARINE & STRUCTURE B07-118 SL
B1: ADMIRALITE MARINE & STRUCTURE
C1: B07-118 SL

--ron

On Wed, 30 May 2007 08:35:02 -0700, klafert
wrote:

This worked for the most part, but for some reason there are some that it
gave me a error on the company name but gave me the bid # or some didnt give
me anything. The error is #VALUE!. I tried to look up the command but I
don't know what the function is called. Can you explain what the formula
means or ... I know while some didnt because they only had company names and
not the bid number but that is fine I can copy those over to the next
column!! But for the most part this worked great!!

Thanks Mary

The functions used are MID, LEFT and SEARCH. Their explanation can be found
under Excel HELP.

One cause for a VALUE error is if the input does not match the specifications
you gave in your original post.
--ron