Result "x" in Column A if Column B starts with a number

[mlarson]

Hello everybody! I hope somebody can help me with this problem. Thanks in advance for taking a look.

I would like a formula that returns "x" in Column A for any cell from Column B that is in the same row and starts with a number, and return blank if it starts with a letter. For example...
If B5 contains "34banana" then I would want an "x" to be the result in A5.
If B28 contains "banana" then I want blank to be the result in A28.
If B99 contains "127. amsterdam" then I want A99 to be "x".

Thanks again for your help!

 

[etaf]

=ISNUMBER(LEFT(B1,1)*1)
=IF(ISNUMBER(LEFT(B1,1)*1),"x","")

Book7
	A	B
1	x	34banana
2		banana
3	x	127
4		banana
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=IF(ISNUMBER(LEFT(B1,1)*1),"x","")

NOTE, after this evening xmas eve, I will be seeing family for a few days, back on 30th Dec
So i may not reply now until then - sorry if i have misunderstood - or leave hanging
Hopefully other members will pickup before

 

[mlarson]

That worked perfectly, thank you!

 

[etaf]

you are welcome

 

[Rick Rothstein]

Assuming the text always starts with either a digit or a letter, and that you do not copy the formula past the end of the data, then you could also use this formula...

=LEFT("x",LEFT(B1)<="9")

 

[Phuoc]

Or try:

=TEXT(LEFT(B1),"x;;;")

 

[Rick Rothstein]

Or try:

=TEXT(LEFT(B1),"x;;;")

NICE!!!!!

 

[Tom.Jones]

Or try:

=TEXT(LEFT(B1),"x;;;")

Hi,

0KKL
If Item start with zero, formula fail.

Assuming the text always starts with either a digit or a letter, and that you do not copy the formula past the end of the data, then you could also use this formula...

=LEFT("x",LEFT(B1)<="9")

iF there are empty cell your formula put an X

etaf formula is OK

 

[Phuoc]

If Item start with zero, formula fail.

=TEXT(LEFT(B1),"x;;x;")

 

[Rick Rothstein]

iF there are empty cell your formula put an X

Okay, this will fix it...

=LEFT("x",LEFT(B1&"x")<="9")

although I still like Phuoc's (now corrected) formula better.