extracting text from cells

  • Thread starter Thread starter famdamly
  • Start date Start date
F

famdamly

I need a formula to reside in cell F1 that simply extracts only the text
and not the numbers from cell A1 The value in A1 should remain
unchanged. I know this is too easy. I should be able to look this one
up, but my brain is fried.
 
Give an example of what the values in column A look like. If you have
something like 123abc789 and it always in this format, then use Left and
Right functions. If you have a mix of something like 12ab1cd34, 1ab23,
1a2b3c4d we got a problem. Maybe use Text to Column.

HTH
Regards,
Howard
 
The combos of letters and numbers vary.

I just need to check a1 to see if it contains certain words. I thought
it would be easier to pull the text into another cell then check that
text for a match.
There must be a way to check a1 for a partial match.

For lack of a better example.
if a1(? contains ?"label",0)

Followed with another if to check if it's something more significant
like

if a1(? contains ? "item","item")


With the ultimate goal of weeding out significant data for another
operation.
 
I"m not quite sure where you're going with your request, but in my
experiments I came up with something you might be able to use:

Here's an example:
A1: The quick brown fox
A2: The quick fox
A3: The fox
A4: The dog

This case-insensitive formula checks if the text contains BOTH "quick"
and "fox"
B1: =SUMPRODUCT(--ISNUMBER(SEARCH({"quick","fox"},A1)))=2

If you need case-sensitive, then:
B1: =SUMPRODUCT(--ISNUMBER(FIND({"quick","fox"},A1)))=2

Copy/paste that formula down through B4.

Results:
B1: TRUE
B2: TRUE
B3: FALSE
B4: FALSE

Something you can use?

Regards,
Ron
 
First of all thanks to everyone for the assistance.

Perhaps it would be easier to show an example of sample data and the
desired result.

Sample data Desired result


A1 label3 F1 label
A2 item5 F2 item
A3 label2 F3 label
A4 6r F4 r
A5 3p F5 p
 
See if this is something you can work with:

F1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{"*label*","*item*"})*{1,2})+1,"neither","label","item","both")


Regards,
Ron
 
Hi!

I'm sure someone has a nice little UDF that does this. In the
meantime.....how about some real fun with a couple of hacks?

If you want the final result to be in column F you need an additional helper
column. I'll use column G for the example:

Enter this formula in G1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,""),2,""),3,""),4,""),5,"")

Enter this formula in F1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G1,6,""),7,""),8,""),9,""),0,"")

Now, for a real "classic" hack that doesn't need a helper column but is
specific to where the original data is and where you want the extracted
text:

Create this named formula:
Name: subst
Refers to:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDIRECT("rc[-5]",FALSE),6,""),7,""),8,""),9,""),0,"")

Then, enter this formula in F1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(subst,1,""),2,""),3,""),4,""),5,"")

In the named formula the reference: INDIRECT("rc[-5]",FALSE), refers to the
cell in the same row as the formula and 5 columns to the left. So, if the
formula is entered in cell F1, INDIRECT("rc[-5]",FALSE), refers to cell A1.
Same row, 5 columns to the left.

Biff
 
Well, Biff

When I looked at your response and saw how completely different it wa
from mine, it made me pause and reread the request. That's when
realized how far off the mark my initial interpretation was! You'r
right about the UDF.

Best regards,
Ro
 
Hi famdamly,

Without going to the compexity of regular expressions, my Excel add-in
"inspector text" has two easy ways to get what you're asking for:

=itEXCLUDE(A1,"0","1","2","3","4","5","6","7","8","9")

and

=itREPLACE(A1,"[0-9]","",,TRUE)

Both of them give these results to your examples:

label
item
label
r
p

Also, both of them remove all instances of all digits, for example they
would return "label" for "lable123", and "item" for "01i23t45e67m89".

Here are the function references for itEXCLUDE and itREPLACE:

http://precisioncalc.com/it/itEXCLUDE.html
http://precisioncalc.com/it/itSEARCH.html

You can download the free edition of inspector text from here, and use
it as long as you wish:

http://PrecisionCalc.com


Good Luck,

Greg Lovern
http://PrecisionCalc.com
More Power In Excel
 
Back
Top