If Statements - Pick up Alpha Characters
 

Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre Ref # IF Statement Answer

1234 0012 1234
2345 0014 2345
2346 9987 2346
TED 3865 3865 ** Show Ref # as Cost Centre has Letters in
it.
4678 3678 3678

Many Thanks in advance.

If Statements - Pick up Alpha Characters
 

Louise,

ISTEXT will do that

=IF(ISTEXT(A1),B1,"")
Drag down

Mike

"Louise" wrote:

Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre Ref # IF Statement Answer

1234 0012 1234
2345 0014 2345
2346 9987 2346
TED 3865 3865 ** Show Ref # as Cost Centre has Letters in
it.
4678 3678 3678

Many Thanks in advance.

If Statements - Pick up Alpha Characters
 

Try something like this in C2:

=IF(A2="","",IF(ISERROR(A1+1),"Ref #",A2))

then copy down as required. Relies on the fact that you can't add a
number to a string.

Hope this helps.

Pete

On Jun 30, 12:31*pm, Louise wrote:
Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre * Ref # * *IF Statement Answer

1234 * * * * * *0012 * * *1234
2345 * * * * * *0014 * * *2345
2346 * * * * * *9987 * * *2346
TED * * * * * * 3865 * * *3865 * ** Show Ref # as Cost Centre has Letters in
it.
4678 * * * * * *3678 * * *3678

Many Thanks in advance.

If Statements - Pick up Alpha Characters
 

Maybe instead,

=IF(ISTEXT(A1),B1,A1)

Mike

"Mike H" wrote:

Louise,

ISTEXT will do that

=IF(ISTEXT(A1),B1,"")
Drag down

Mike

"Louise" wrote:

Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre Ref # IF Statement Answer

1234 0012 1234
2345 0014 2345
2346 9987 2346
TED 3865 3865 ** Show Ref # as Cost Centre has Letters in
it.
4678 3678 3678

Many Thanks in advance.

If Statements - Pick up Alpha Characters
 

Unfortunately there is no IsAlpha function available with VBA like in C++.
But there is a IsNumeric() function available, which you could use.

If (IsNumeric(...) = false) Then ...

Test if the content is numeric. If so do nothing. If not (false) then "Show
Ref #"


"Louise" wrote:

Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre Ref # IF Statement Answer

1234 0012 1234
2345 0014 2345
2346 9987 2346
TED 3865 3865 ** Show Ref # as Cost Centre has Letters in
it.
4678 3678 3678

Many Thanks in advance.

If Statements - Pick up Alpha Characters
 

Sorry, I misread what you wanted:

=IF(A2="","",IF(ISERROR(A1+1),B2,A2))

Hope this helps.

Pete

On Jun 30, 12:46*pm, Pete_UK wrote:
Try something like this in C2:

=IF(A2="","",IF(ISERROR(A1+1),"Ref #",A2))

then copy down as required. Relies on the fact that you can't add a
number to a string.

Hope this helps.

Pete

On Jun 30, 12:31*pm, Louise wrote:



Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre * Ref # * *IF Statement Answer

1234 * * * * * *0012 * * *1234
2345 * * * * * *0014 * * *2345
2346 * * * * * *9987 * * *2346
TED * * * * * * 3865 * * *3865 * ** Show Ref # as Cost Centre has Letters in
it.
4678 * * * * * *3678 * * *3678

Many Thanks in advance.- Hide quoted text -

- Show quoted text -

If Statements - Pick up Alpha Characters
 

Hi

With cost centre in column A and Ref# in column B enter this in C2 and copy
down

=IF(ISNUMBER(A2),A2,B2)

Regards,
Per

"Louise" skrev i meddelelsen
...
Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need
to
pick up another cell.

Example of what I have:

Cost Centre Ref # IF Statement Answer

1234 0012 1234
2345 0014 2345
2346 9987 2346
TED 3865 3865 ** Show Ref # as Cost Centre has Letters
in
it.
4678 3678 3678

Many Thanks in advance.

If Statements - Pick up Alpha Characters
 

And STILL I got it wrong:

=IF(A2="","",IF(ISERROR(A2+1),B2,A2))

Need a proof-reader !! <bg

Pete

On Jun 30, 12:48*pm, Pete_UK wrote:
Sorry, I misread what you wanted:

=IF(A2="","",IF(ISERROR(A1+1),B2,A2))

Hope this helps.

Pete

On Jun 30, 12:46*pm, Pete_UK wrote:



Try something like this in C2:

=IF(A2="","",IF(ISERROR(A1+1),"Ref #",A2))

then copy down as required. Relies on the fact that you can't add a
number to a string.

Hope this helps.

Pete

On Jun 30, 12:31*pm, Louise wrote:

Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre * Ref # * *IF Statement Answer

1234 * * * * * *0012 * * *1234
2345 * * * * * *0014 * * *2345
2346 * * * * * *9987 * * *2346
TED * * * * * * 3865 * * *3865 * ** Show Ref # as Cost Centre has Letters in
it.
4678 * * * * * *3678 * * *3678

Many Thanks in advance.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

If Statements - Pick up Alpha Characters
 

Hi Mike,

This works perfectly - THANK YOU SO MUCH - You've just saved me hours of
manual work! :)

************************************************** ******

"Mike H" wrote:

Maybe instead,

=IF(ISTEXT(A1),B1,A1)

Mike

"Mike H" wrote:

Louise,

ISTEXT will do that

=IF(ISTEXT(A1),B1,"")
Drag down

Mike

"Louise" wrote:

Hi,

Can anyone tell me if there is a way to pick up (using an IF statement)
whether a cell has any letters in it, if a cell has letters in it I need to
pick up another cell.

Example of what I have:

Cost Centre Ref # IF Statement Answer

1234 0012 1234
2345 0014 2345
2346 9987 2346
TED 3865 3865 ** Show Ref # as Cost Centre has Letters in
it.
4678 3678 3678

Many Thanks in advance.