| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find alpha all characters | Excel Discussion (Misc queries) | |||
| Ranking Alpha Characters | Excel Discussion (Misc queries) | |||
| How can I pick up characters after the first 4 in Excel cell | Excel Discussion (Misc queries) | |||
| Alpha characters problem? | Excel Worksheet Functions | |||
| Check for Alpha characters | Excel Discussion (Misc queries) | |||
Linear Mode
