Separate the number into a column and remove the yes no

[philky001]

I have an excel sheet with only one column like this:
Col A
1201NSNYC-Brooklyn Group HHCyes
1202NSNYC-Brooklyn Office HHCCyes

I would like to run a formula on the dataset that would separate for me the 1201 all rows start out with such a number.
So that the new column would contain 1201 and it would delete itself from the original col. also if we can delete the yes at the end (sometimes it is no ) but mostly is yes so it can
be done just for yes. Thank you for your help.
The result after the formula runs is:

Col A Col B
1201 NSNYC-Brooklyn Group HHC
1202 1202NSNYC-Brooklyn Office HHCC

 

[philky001]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Excel 2016

 

[Joe4]

Try:

Select your A COL click on Data at the top, click on "text to Column" in the Text to columns wizard select fixed width, Next. move the Vertical line over between the numbers and "NSNCY" click Finish

Then use this formula in column C

=LEFT(B1,SEARCH("YES",B1)-1)

drag it down. copy column C and paste as value and remove column B

hope this helps

That formula won't work, for the same reason I mentioned up above (if those characters may be found in the middle of the string).
Also, if "Yes" is not found at all, it returns an error.

 

[hajiali]

That formula won't work, for the same reason I mentioned up above (if those characters may be found in the middle of the string).
Also, if "Yes" is not found at all, it returns an error.

how about if we added the if statement like

=IF(RIGHT(B1,3)="yes",LEFT(B1,SEARCH("yes",B1)-1),LEFT(B1,SEARCH("no",B1)-1))

 

[philky001]

Try:

Select your A COL click on Data at the top, click on "text to Column" in the Text to columns wizard select fixed width, Next. move the Vertical line over between the numbers and "NSNCY" click Finish

Then use this formula in column C

=LEFT(B1,SEARCH("YES",B1)-1)

drag it down. copy column C and paste as value and remove column B

hope this helps

this is good but how do I get to keep the new column (without the YES). It remains as a ref#

 

[Joe4]

Here is the formula I came up with for column C to handle both the "yes" and "no":

=IF(RIGHT(A2,3)="yes",MID(A2,5,LEN(A2)-7),IF(RIGHT(A2,2)="no",MID(A2,5,LEN(A2)-6),MID(A2,5,LEN(A2)-4)))

 

[hajiali]

Ignore Post #13 still same issue you mentioned Joe4. I get what your talking about

 

[Joe4]

I think if you use the formula fluff provided for column B in post 2, and the formula I provided for column C is post 15, you should have what you need.

Note. If you want column B to be text and not numeric, just remove the "+0" from the formula, i.e.

=LEFT(A2,4)

You may need to do that if your 4 digits may contain leading zeroes that you do not want to lose.

 

[FormR]

Just another option (a slight twist on the original suggestion) for the yes/no problem.

=SUBSTITUTE(SUBSTITUTE(MID(A2&"^",5,100),"yes^",""),"no^","")

The "^" character should be replaced by a character that won't appear in the text strings.

 

[philky001]

Col B is fine. The only issue is C remains as a formula so that if I delete B i don't have the data. But this has been very helpful, I can manage at this point. Thank you kindly.

 

[Joe4]

Just another option (a slight twist on the original suggestion) for the yes/no problem.

=SUBSTITUTE(SUBSTITUTE(MID(A2&"^",5,100),"yes^",""),"no^","")

The "^" character should be replaced by a character that won't appear in the text strings.

I like that formula. Just one small amendment, in case the string does not end in either "yes" or "no"

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1&"^",5,100),"yes^",""),"no^",""),"^","")

Otherwise, it will have a "^" on the end when the string does not end in either "yes" or "no".