extracting text from cells
 

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.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

Try typing in =F1 into the cell press enter.
Maybe this will work

"famdamly" wrote:


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.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

or =A1 in the cell you want the data in

"famdamly" wrote:


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.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

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

"famdamly" wrote in
message ...

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.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile:
http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

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.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

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


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

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(SUBST ITUTE(A1,1,""),2,""),3,""),4,""),5,"")

Enter this formula in F1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(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(SUBST ITUTE(INDIRECT("rc[-5]",FALSE),6,""),7,""),8,""),9,""),0,"")

Then, enter this formula in F1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(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

"famdamly" wrote in
message ...

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


--
famdamly
------------------------------------------------------------------------
famdamly's Profile:
http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

Well, Biff

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

Best regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=516690

extracting text from cells
 

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



famdamly wrote:
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

extracting text from cells
 

Ron, that worked well. I'm sure I will be able to work with that. Thanks
alot.:)


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690