Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

=FIND

Posted by J.R. on December 19, 2000 3:53 AM
I am trying to break down a string to a number of components. The string, residing in r1c1 looks like this:

"ComponentXYZ - 126578 - IBM Corporation.121501.567"

I want to find the "-" and then split out the company name and product. I get an error message when I use the myString=FIND("-",A1,1) function.

I appreciate any comments and help.

J.R.


Check out our Excel Resources

Re: =FIND

Posted by Aladin Akyurek on December 19, 2000 5:42 AM

Can you tell what the result should be wtr your example string?

Thanks.

Aladin


Re: =FIND

Posted by J.R. on December 19, 2000 5:32 PM

My objective is to break down the string into six parts:

Part Name:ComponentXYZ
Part #:126578
Company Name:IBM Corporation
Company Number:121501
Company Code: 567

Thanks,

J.R.


Re: =FIND

Posted by Aladin Akyurek on December 19, 2000 11:29 PM

Assuming a regular composition of strings and the first string being in A2, the following formulas appear to work:

B2 =MID(A2,1,FIND(" -",A2,1)-1) [ gives Part Name ]
C2 =RIGHT(A2,LEN(A2)-FIND("-",A2,1))
D2 =LEFT(C2,FIND(" -",C2,1)) [ gives Part # ]
E2 =RIGHT(C2,LEN(C2)-FIND("-",C2,1))
F2 =MID(E2,1,FIND(".",E2,1)-1) [ gives Company Name ]
G2 =RIGHT(E2,LEN(E2)-FIND(".",E2,1))
H2 =LEFT(G2,FIND(".",G2,1)-1) [ Company # ]
I2 =RIGHT(G2,LEN(G2)-FIND(".",G2,1)) [ Company Code ]

ps. an example file is underway to you thru e-mail.

Aladin


Re: =FIND

Posted by J.R. on December 21, 2000 10:26 AM

I think this will work fine. Thanks a lot for your kind help.

J.R.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.