I need to look at cells in column A to find the number pattern of NN-NNN and then copy that to cells in column c. e.g.- Column A has "SPREAD_11_PLAT_SHTS_22-315_IFR" and I need to copy the 22-315 to cell in column C.
-
Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed.– bwDracoOct 2, 2015 at 19:20
-
John is there always a single - to search for? Knowing more about the possible positioning of the pattern you are searching for will help– datatooOct 2, 2015 at 19:53
-
Can the hyphen appear only within the pattern or can there be other hyphens in the string? Do all cells in col A contain the pattern?– fixer1234Oct 3, 2015 at 1:20
Add a comment
|
2 Answers
If your example is representative, and a hyphen will be found only within this number pattern, you could use:
=MID(A1,FIND("-",A1)-2,6)
This just finds the position of the hyphen in the column A string, and then takes six characters, starting two characters before the hyphen.
answered Oct 3, 2015 at 1:37
John
Not sure where the - falls in the field, but the following formula would work if you had either 2 or 3 numbers before or after the '-'
=MID(A1,FIND("_",A1,FIND("-",A1,1)-4)+1,FIND("_",A1,FIND("-",A1,1))-FIND("_",A1,FIND("-",A1,1)-4)-1)
Attached is a visual of what I mean
