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

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1
2	1201NSNYC-Brooklyn Group HHCyes	1201	NSNYC-Brooklyn Group HHC
3	1202NSNYC-Brooklyn Office HHCCyes	1202	NSNYC-Brooklyn Office HHCC
4	1201NSNYC-Brooklyn Group HHCno	1201	NSNYC-Brooklyn Group HHC
5	1202NSNYC-Brooklyn Office HHCCno	1202	NSNYC-Brooklyn Office HHCC
Master

Cell Formulas
Range	Formula
B2:B5	B2	=LEFT(A2,4)+0
C2:C5	C2	=SUBSTITUTE(SUBSTITUTE(MID(A2,5,100),"yes",""),"no","")

 

[Joe4]

Are you looking for a manual way to do this, or VBA?

If the number to be separated is always exactly 4 characters long, you can easily separate it using "Text To Columns", choosing the Fixed Width (space delimited) option, and making the split right after character 4.

We can use formulas to remove the "yes" or "no" off the end of records (using IF and RIGHT functions).

 

[philky001]

Sorry this is clearer
I have an excel sheet with only one column like this:

Col A

1201NSNYC-Brooklyn Group HHCyes

1202NSNYC-Brooklyn Office HHCyes


I would like to run a formula on the dataset that would separate for me the 1201, 1202 all rows start out with such a number. the first 4 characters will always start with such a number.

So that the new column would contain 1201, or 1202 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 NSNYC-Brooklyn Office HHC

 

[philky001]

Are you looking for a manual way to do this, or VBA?

If the number to be separated is always exactly 4 characters long, you can easily separate it using "Text To Columns", choosing the Fixed Width (space delimited) option, and making the split right after character 4.

We can use formulas to remove the "yes" or "no" off the end of records (using IF and RIGHT functions).

Manual or VBA. It will always start with a 4 digit number.

 

[Fluff]

Have you tried the formulae I suggested?

 

[Joe4]

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

Fluff,
I see a potential problem with that formula. And that is if the text contains the letters "yes" and "no" before the end, like a city name like "Reno", it is going to mess it up.
Which is why I was thinking we might need to use the RIGHT function to make sure we are just checking the last 2 or 3 letters.

 

[Fluff]

if the text contains the letters "yes" and "no" before the end, like a city name like "Reno"

Good point.

 

[Fluff]

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’)

 

[hajiali]

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