Forum Discussion
Returning Text from a data cell
Here are formulas that will work in older versions of Excel too.
With a value such as US351-4W in A1:
=--LEFT(MID(A1, FIND("-", A1)+1, 100), MATCH(FALSE, ISNUMBER(--MID(MID(A1, FIND("-", A1)+1, 100), ROW(INDIRECT("1:"&LEN(MID(A1, FIND("-", A1)+1, 100)))), 1)), 0)-1)
and
=MID(MID(A1, FIND("-", A1)+1, 100),MATCH(FALSE, ISNUMBER(--MID(MID(A1, FIND("-", A1)+1, 100), ROW(INDIRECT("1:"&LEN(MID(A1, FIND("-", A1)+1, 100)))), 1)), 0), 100)
I agree with Detlef_Lewin . Flash Fill is the best solution here. A lengthy formula can be written but Flash Fill can be summoned with the press of a button at will.
- I need to have formulas in case any extra data comes in. These cells do not have a set pattern to them as it can have several numbers or letters to them based on where the data comes in from.
- So you did not try Flash Fill?
It's entirely possible that danielS1275 doesn't know what Flash Fill is. I didn't, and I'm a regular on these boards. I looked at Excel Help and see that it's available, but haven't tried to figure it out.
Might I suggest that one of you give him (and, in the process, me) a pointer or two on how to use it.
danielS1275 You wrote as a follow-on to your original:
These cells do not have a set pattern to them as it can have several numbers or letters to them based on where the data comes in from.
So it's possible that my formulas, based on your original set of examples, would need to be modified to add nuances. But they should point you in the right direction.
