Forum Discussion
Solved
Returning Text from a data cell
Hi all, I have been trying to find a set of formulas that will extract certain numbers or text from data cells and returns them to a separate column but I have had no luck so far. This is an exce...
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 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.
- The online help article is the first place to go.
Second step would be a web search and consulting other resources.
If it is a simple pattern, FF will kick in after you typed the second example.
For more complex patterns you may need more examples and/or press CTRL-E.
