1

I need to split text by the first space. But since some rows don't either have multiple words with space between them or are empty, I get a #VALUE error for both the right and left column. How can I fix the errors? I use this for the text on the left of character:

=LEFT(A1,FIND(" ",A1)-1)

and this for the text on the right of character:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Here's a sample image: enter image description here

and here's the sample file (just FYI):

1
  • Are you sure you're putting a comment to the right question? Nov 7, 2019 at 8:23

2 Answers 2

0
B1=IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))
C1=TRIM(MID(A1,1+LEN(B1),LEN(A1)))

enter image description here

enter image description here

To avoid zero value in column B for local Excel application if a cell in column A is empty use

B1=IF(ISERROR(FIND(" ",A1)),A1&"",LEFT(A1,FIND(" ",A1)-1))
12
  • Are you sure this is an answer to my question? Nov 7, 2019 at 8:23
  • Formula in C1, returns BLANK ,, @Akina pls check the formula !! Nov 7, 2019 at 8:24
  • @ImanGhavamabadi,, you want to split text by 1st space or anything else ?? Nov 7, 2019 at 8:25
  • Split by the first space. The first word in one column and the rest in another. Problem is, if there's only one word in a cell, it throws a #VALUE error. In addition, if it there isn't any text in a cell, it throws the same error. Nov 7, 2019 at 8:28
  • 1
    @ImanGhavamabadi it's throwing a '0' when in B column if the A cell is empty. Yes, GoogleSheets acts correctly whereas local Excel autoformats empty string to number type obtaining zero. Check corrected formula.
    – Akina
    Nov 7, 2019 at 8:54
-1

Your used formula with little adjustments works properly:

enter image description here

  • Formula in cell R34:

    =IF(Q34="","",IF(LEN(TRIM(Q34))-LEN(SUBSTITUTE(Q34," ",""))+1=1,Q34,LEFT(Q34,FIND(" ",Q34)-1)))
    
  • Second formula in cell S34:

    =IF(Q34="","",IF(LEN(TRIM(Q34))-LEN(SUBSTITUTE(Q34," ",""))+1=1,"",RIGHT(Q34,LEN(Q34)-FIND(" ",Q34))))
    

You may adjust cell references as needed.

1
  • Please write your concern that why this has been Down voted? Nov 7, 2019 at 9:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .