Help with Excel Functions

I'm sorry if this is the wrong place for this so just slap me with a splintered ruler if so.

Can anyone tell me which function I should research to extract a set of hyphenated numbers out of a cell that is full of text and numbers? I have exported the text from the Operation Specifications field which contains some "Spec Numbers" that I need to extract so that I can then import those numbers back into the operations UDF. There are many that have a specific prefix 40001-xxx-xx but there are others that may be random or not hyphenated. I realize I may have to use several variations for the differnt types of numbers but just getting a formula that works for one would be great so I can modify it as needed for the others.

Thanks.
Paul

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

denise

You will be needing several formulas... Probably some combination of the following;
=left()
=right()
=mid()
=find()
=len()
Example; If cell A1 contained "abc40001-xxx-xx"
B1 could contain the formula =find("40001") which return 4
C1 could contain the formula =mid(A1,B1,12) which would return "40001-xxx-xx"
You could blend those two formulas together, but I like to do them in steps, in separate columns.
You could also use =Right(A1,12), if it ALWAYS at the end.....
Lots of ways to mix and match.

unknown

Check these forums:

stackoverflow.com/.../extract-substring-from-a-string-in-vba

www.mrexcel.com/.../298184-how-extract-substring-string-based-substring-length.html

stackoverflow.com/.../extract-part-of-a-string-in-excel-cell

www.ozgrid.com/.../extract-words-function.htm

unknown

Thanks. I was afriad fo that. The text is extremly random so that the numbers can be located anywhere. Not always on the left or right and anywhere in the middle so using location numbers wont work.
Is there a way to pull just strings of numerical values with out the alpha? Although there are other specs that are alph-numeric.

Ok, thanks for trying. I really apreciate the quick response. I'll have to figure something else out.

0712131256598360.doc

unknown

Tom, thanks but VBA is so far over my head I don't dare without proper training. Looks like that would be the ticket though.

Paul

unknown

Check out all the text functions in Excel :
office.microsoft.com/.../text-functions-reference-HP010079191.aspx

Richard Lagoy

Try this. Create a new column next to the exported column and use the formula below:

=SUBSTITUTE((A1),"-","")

For example, the formula above in column B1 will change YYYY-34-84747 to YYYY3484747. The column is the formula is not absolute so can be copied to all cells in column B and therefore will convert the value in the associated column A.

Hope that helps.

unknown

This will work:

Cell A1's value is abc moq prstf 40001-xxx-xx brg fpa

Cell B1 formula: =FIND("40001",A1) {returns 15, position of the beginning of the string 40001) {The key is to identify the particular string you need to find, whether it is 40001, MB5, BR5AS, whatever. Using quotations in the FIND function will allow it to find number, text, symbols, or combination thereof.}

Cell C1 formula: =MID(A1,B1,12) {returns 40001-xxx-xx} {the last parameter in the MID function, 12, tell how many characters after the first character to return: 4 would return "4000", 7 would return "40001-x", etc.}

[Updated on 1/2/2014 1:04 PM]

unknown

Paul,
As you can see there are a number of ways to do this. What's best? It depends on the complexity of the data and your skill set.
I like rwoodard's approach. You can use the Find function in Excel to find the location of a piece of text. For example in the text "blah blah 40001" the 40001 is at location 11 (the 11th character). Then you can use the Mid function to extract the text starting at that point (in this example 11) and as many characters after that as you specify.
You might need to copy the Find data into a value for the Mid function to work.
I see that Tom Huntford has described this quite well in his most recent post.

unknown

EUREKA! Tom, that's what I was looking for. Perfect! Now I just have to figure out how to use it on the other stuff.

Yippie!

Thanks Tom and everyone,
Paul

unknown

pe44bach: Glad it helped! It is a blessing to be able to help (and welcome to the club of trying to find out "how do I do this in Excel???")

For the other stuff:

1. Determine what you are looking for at the beginning of the string, and use that in the FIND function: FIND("A3R-",A1), FIND("001/13"), FIND("FH SCREW 3/8"), etc.

2. Determine how long a string you need and use it in the MID function.

If parts are like "A3R-xx-xxxx" (10 characters), use MID(A1,B1,10).
If parts are like "001/13/BRQ-0000013" (18 characters), use MID(A1,B1,18).
If parts are like "FH SCREW 3/8 X 2 X NC SS PASSIVATED" (35 characters), use MID(A1,B1,35)

Etc.

Merry 7th day of Christmas!

unknown

Text to Columns is another feature in excel I use all the time. It would defiantly work with the hyphen.

Dave

unknown

I like that too. Thanks Dave!

Paul

Copyright © 2025 Infor. All rights reserved.