Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jim Jim is offline
external usenet poster
 
Posts: 615
Default =TRIM(LEFT(B47,FIND(" ",B47)-1))

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,276
Default =TRIM(LEFT(B47,FIND(" ",B47)-1))

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default =TRIM(LEFT(B47,FIND(" ",B47)-1))

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default =TRIM(LEFT(B47,FIND(" ",B47)-1))

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,346
Default =TRIM(LEFT(B47,FIND(" ",B47)-1))

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
=TRIM(LEFT(B50,FIND(" ",B50)-1)) Jim Excel Discussion (Misc queries) 4 January 14th 10 11:56 PM
"Find" a wildcard as a place marker and "replace" with original va Eric Excel Discussion (Misc queries) 1 January 27th 09 07:00 PM
=SUMPRODUCT(--(TRIM(A1:A10)<"")), what does the "--" mean? Ray Excel Worksheet Functions 2 August 28th 08 09:05 AM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 02:54 AM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 03:23 AM


All times are GMT +1. The time now is 06:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"