Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hello,
How do I write this formula so that the result is a blank cell if B47 is blank? I get a #VALUE now. =TRIM(LEFT(B47,FIND(" ",B47)-1)) Thanks so much for hte help |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
=if(B47="","",TRIM(LEFT(B47,FIND(" ",B47)-1))) "Jim" wrote: Hello, How do I write this formula so that the result is a blank cell if B47 is blank? I get a #VALUE now. =TRIM(LEFT(B47,FIND(" ",B47)-1)) Thanks so much for hte help |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Another way:
=TRIM(LEFT(B47,FIND(" ",B47&" ")-1)) This will protect against B47 not having any space character, too. Jim wrote: Hello, How do I write this formula so that the result is a blank cell if B47 is blank? I get a #VALUE now. =TRIM(LEFT(B47,FIND(" ",B47)-1)) Thanks so much for hte help -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
And if you're going to use trim, you don't need to subtract 1. The =trim() will
remove the trailing space. =TRIM(LEFT(B47,FIND(" ",B47&" "))) Jim wrote: Hello, How do I write this formula so that the result is a blank cell if B47 is blank? I get a #VALUE now. =TRIM(LEFT(B47,FIND(" ",B47)-1)) Thanks so much for hte help -- Dave Peterson |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
If I understand correctly you want to keep everything upto the first blank: Select the cell or range (B47 here) and choose Data, Text to Columns, Delimited, Next, specify Space as the delimiter and click Next. Select all columns to the right of the first column in the preview area and choose Do not import, Skip. Set a destination cell and click OK. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jim" wrote: Hello, How do I write this formula so that the result is a blank cell if B47 is blank? I get a #VALUE now. =TRIM(LEFT(B47,FIND(" ",B47)-1)) Thanks so much for hte help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
=TRIM(LEFT(B50,FIND(" ",B50)-1)) | Excel Discussion (Misc queries) | |||
"Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
=SUMPRODUCT(--(TRIM(A1:A10)<"")), what does the "--" mean? | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) |