How to split data in one column into two

Hi Excel Forum

So it's been a long time since I've used Excel and I have a column that begins with Yes and No values followed by additional text, e.g. (Yes - No data). What I want to do is separate the yes/no values in one column and the additional text into another. I was able to insert a new column and paste the previous column's information into it as a safe guard and make that the new comments column. So my question is how do I remove text following either yes or no from the first column? Thank you.

Hi everyone, thank you for the replies, i look forward to trying them out. But here's an embarrassing question. To input those formulas, do I click on the top of the cell (e.g. "R1"), right click and select format cells? thank you all

Oh sorry I thought that's what they were asking.
You would enter this into the first cell of the column you want the data to be in, make sure the reference is set to the column the data is currently in, and then drag it down for however many rows you have data.

Hey guys,

So the first formula:

Please Login or Register  to view this content.

I got a message box saying Circular Reference Warning and changed "No" to 0.

I got the same message with:

Please Login or Register  to view this content.

Then I tried:

Please Login or Register  to view this content.

And I got The name that you have entered is not valid and lists a bunch of reasons why

Does anyone know how to fix these errors??

Which cell are you trying to enter these formulas in? Are you trying to enter this in cell R2? The first one definitely works for me (returns yes or no).
For the last one, try:
=MID(R2,IF(LEFT(R2,3)="Yes",4,3),99)

I'm guessing you entered it into "R2" as I can't think of any other reason you'd get a circular reference. Formulas cannot reference the cells they are placed in, so you're going to have to enter this formula into a different blank column (e.g. "S2"). If you really want your data to be in column "R", you can then copy column "S" and paste the values into "R".

The blue highlight means it is interpreting it as a cell. You probably posted it at the same time I posted my explanation (see above) so you didn't notice it. You're not going to be able to post this in "R2".

Some of the cells in the column are blank

So I tried:

Please Login or Register  to view this content.

And it changes everything to Yes including cells with No

Would it be possible for you to attach a sample workbook (or at least a screenshot of one) showing a subset of your data (with personal info changed/deleted), and showing what results you would like to achieve (you can enter it manually for the example)

yesandno.PNG

Here you are sir. As mentioned I inserted another column which will be the 'comments' column and pasted everything into it. for example S11 should say "(Jan 18/17)" minus the "yes" before it.

So just to clarify, your total data (Yes/No + text) is in column R, you want the text in column S, and the "Yes/No" somewhere else (column T maybe)?

Exactly!

Sorry if I wasn't clear in my explanation

I'm uploading a workbook showing the solution to the sample. I just entered
=MID(R2,IF(LEFT(R2,3)="Yes",4,3),99)
into "S2", and
=IF((LEFT(R2,3)="YES"),LEFT(R2,3),LEFT(R2,2))
into "T2", and the dragged the formulas down.

Good morning everyone

Thank you guys. And thanks Daniel, your sample worked perfectly!

Before we wrap this up just a quick question

dates.PNG

I have a similar issue here. There are three date columns (N, O, P). I was thinking of using what Daniel has provided and playing around with this to get one column in a specific date format and the other column to be a comment column. However "ongoing" should be set to plus 10 years (e.g. 2013-04-01 in column N, ongoing = 2023-04-01 in column O). Does anyone have any suggestions? Thank you!

Sorry I'm not really sure what you're asking. It would probably be helpful if you showed what you would like the solution to look like.

Originally Posted by danielexcelvba

Sorry I'm not really sure what you're asking. It would probably be helpful if you showed what you would like the solution to look like.

I'm sorry. Let me try again. In the O column, I was wondering is there a way to change ongoing to 2023-04-01 (because in column N, the date is 2013-04-01) since I was told 'ongoing' is N's date plus ten years. Thank you.

Ah I see. If you can format column "N" as a date that shouldn't be too hard. However, once again, you'd have to put the new dates into a column that isn't "N" or "O" to avoid a circular reference warning. You could try something like this:
=IF(O1="ongoing",(DATE((YEAR(N1)+10),MONTH(N1),DAY(N1))),N1)
This will add 10 years onto the date if column "O" contains "ongoing", and will otherwise just copy the date from column "N". I don't think it will work unless you first format as a date though

Great. Thank you. Now to split column N, I know I'm supposed to do something like this

Please Login or Register  to view this content.

as this is how we split the yes/no field.

Except, instead of yes or no, N has date values that aren't in a consistent format (e.g. 2013-02-21, October 21, 2012, etc.). I tried going Format Cells > Date > 03/14/01 but that didn't do anything. Do you guys know how to do this? If not I don't mind manually entering the dates in the correct format however before I did that I was just curious on what you guys think. Thanks again.

I think having text after your date is what is preventing you from formatting it correctly. Someone on here might know a way to fix it, but I personally don't know how you would format it without erasing everything after the date.

Ok thank you.

Hi

I appreciate that. I downloaded your sample and when I hit "Click me" I got a message saying Cannot run macro[name]. The macro may not be available in this workbook or all macros may be disabled

Thats exactly it. Thank you. It works now.

Awesome! Let me know if it works on your actual workbook (or if you don't know how to run in on that workbook), or if you have dates in other formats that aren't being recognized. I slightly edited the code to include a few more date formats if you want to try the new one

Please Login or Register  to view this content.

Just note that running a macro cannot be undone, so if it screws up your data, simply close and reopen the workbook without saving

You don't actually need a button to run the code, I just added one for simplicity. If you simply want to run the code, you can press Alt + F11 once inside your workbook to open the vba window, then go to insert > module and paste the code in. Once the code is in there, press the green arrow at the top or "F5" to run the code.

If you prefer the button, this will give you an overview of how to add one (you still will need to paste the code into a new module first).
https://support.office.com/en-us/art...rs=en-US&ad=US

Also, the location of the button will not matter.

When I tried running it I got an error saying

Run-time error '9'

Subscript out of range

Please Login or Register  to view this content.

Cells(i, 18).Value = larray(0) is highlighted yellow

I tried changing the 18 to 1550 (number of current records) but that didn't do anything.

I have no idea how that would happen I'm going to start a new post in the vba forum to see if anyone can figure it out.
In the meantime, you could paste values into the sample workbook and then paste the filtered answers back into your workbook.

Thank you!

Ah never mind I think I may have figured it out. Do you have any blank cells in your workbook? That is likely what is throwing it off

Originally Posted by danielexcelvba

Ah never mind I think I may have figured it out. Do you have any blank cells in your workbook? That is likely what is throwing it off

Yes, some of the cells are indeed blank

One small change. It should work now (I hope)

Please Login or Register  to view this content.

I tried running that and I get an error saying Run-time error '13' Mismatch Type and this is what the Final Date column shows

dates3.PNG

As you can see, it kind of works as two new dates are displayed.

Once again, I appreciate all your hard work in trying to help me get this thing going.

Ah yes I forgot to edit the rest of it. You can try this, and if it still gives an error please let me know which part was highlighted again.

Please Login or Register  to view this content.

Looking at your results, for the strange text in column "P", did the corresponding cells in column "N" start with a date?

For the most part yes, except I found a few records that say something with a date (e.g. DO NOT USE - VACCINE REQUIRED 2014-06-16 new non-standard sent to CS 2016-04-08 - KMJ)

Originally Posted by danielexcelvba

Looking at your results, for the strange text in column "P", did the corresponding cells in column "N" start with a date?

odddates.PNG

Here's a few of the odd results

gooddates.PNG

But for the most part the dates are on point

Ah yes the code doesn't handle dates in the Month DD/YYYY format. I can try to edit it so it will though.
But for "D on Dec 1" the cells in column "N" were blank?

I believe so because I haven't entered anything in there. If it helps I can just manually edit those odd records. There aren't many.

Ok so this should resolve the issue of the other date formats, and it not recognizing "ongoing" when it is upper case or part of a longer text. Hopefully it will also resolve the weird texts. Let me know if it works!

Please Login or Register  to view this content.

Ok and just to make sure I'm placing this code in the right spot, Once i hit ALT + F11, under VBAProject([project name]) > Microsoft Excel Objects > Sheet1 ([project name]) > This workbook is what I see. Do I paste that into sheet one or this workbook? Thank you.

Sorry before you try that I had to edit something again (I keep forgetting).

Please Login or Register  to view this content.

And you can paste it into workbook, or create a module and paste it there

Okay and after it's pasted. Do I simply hit the green arrow? Or the stop button and THEN the green arrow? I know noob question but

Just the green arrow should work

Daniel. Thank you very much this worked. However I should note something strange happened. The 3 yes/no columns we discussed earlier in this thread (original, comment, yes/no), suddenly there is no data in all three columns R, S, T and the Yes/No column got shifted over to U with no data as well. I don't mind re doing what we already discussed. However I just felt it might be worth mentioning as I hope these two splits can coexist in the same spreadsheet. Thank you!

Oh I didn't realize these two questions were about the same worksheet, I thought those columns would be blank.
I used those columns as "Helper Columns" for the code (used to store intermediate values), and then deleted them so that you wouldn't be left with random values in them. If you tell me the names of a few blank columns in your workbook I could change it to those instead (e.g. X/Y/Z maybe?)

The only blank columns I have really are AE, AF, and AG. I'm sorry I should have specified earlier.

Should I redo the yes/no dilemma and then copy that code into the worksheet?

If you still have your original worksheet saved (with the yes/no stuff complete), you can just paste the new code into that

Thank you Daniel. This finally seems to all be working now. I apologise for any inconveniences. Thank you.

No problem! Glad I could help

I edited the code from last time to also separate the text from this column. Replace it with the new one and let me know if it works!

Please Login or Register  to view this content.

Edit: do you want the comments deleted from column "X"? Right now they're being put in "Y", but are not being deleted

If you want them deleted (column "X" will now contain dates only), use this

Please Login or Register  to view this content.

That's really strange. You said the code was working before right? Because I didn't edit that part at all

debug.PNG

Hey yeah it was working. But i noticed that the excel format I was working on didn't save Macros then I changed that so it can be saved as one, I think that might have something to do with it.

You must have a date in a format that I haven't tested. Try changing >0 to >1 in the line before the highlight maybe?

Hi

So I made the change and this happened...

debug2.PNG

Some text values from columns we don't need to worry about shifted over. I feel we're getting there.

Really not sure why that would happen
It could just be an issue with it thinking an unused column in the middle of your data is your last column? In which case maybe try this?

Please Login or Register  to view this content.

Hi Daniel, hope you had a great weekend. I tried that latest line of code and I get the same Mismatch Type error, but I think part of it kind of works because column X is the column with the date + text and column y has some of the cells have comments moved into it from x

debug3.PNG expiry3.PNG

Ah right blank cells would cause issues with that. Should be fixed

Please Login or Register  to view this content.

Hi Daniel, for some reason this part always comes back highlighted. What does this bit of code mean exactly?

Cells(i, 24).Value = CDate(Cells(i, 24).Value)

> Type mismatch error

That code is attempting to convert the value in column "X" to a date. You must have values that don't resemble dates at all for it to give you an error. You can just delete this part

Please Login or Register  to view this content.

But then you will be left with dates in non-date formats in column "X" (e.g. Dec 12 2017)

expiry4.PNG I think we're good now thank you. Most of the cells looked like this and most of them are split, I'll manually edit the odd one here and there but this pretty much does the trick. Once again thank you. Sorry for being a pain

No worries, glad it somewhat worked for you!