0

I have a very bad Excel sheet which I try to fix.

Instead of getting separated columns, I got lines which I separated into different columns using ''text to columns.''

Now I have a problem, because one of the columns has different length and I can't use text to columns in order to separate the data I need.

Here you can see the data I need to extract and the different locations in has:

here you can see the data I need to extract and the different locations in has

This is how my column looks like, I need the dates like the one that marked in red, but as you can see, down in the list, the location of the dates is different, so I can't use the ''text to columns'' how can I solve it

3
  • The attached screen shot is original data or the result,,if not original please Edit your question and add sample data for better understanding. Aug 18, 2019 at 12:22
  • This can be solved using Text to Column, but first confirm that Screen Shot data is original or what! Aug 18, 2019 at 12:28
  • It sounds like you are using the "Fixed width" option of Text to Columns. You should probably use the "Delimited" option and specify ; and _ as the delimiters. If you don't want the delimiters to be removed, you will need to replace them after the split.
    – Blackwood
    Aug 18, 2019 at 12:51

1 Answer 1

0

Try this method:

  1. Select Column- go to Data- Text to Columns- Select Delimited- check Other and enter ;- Finish
  2. Move Column C to Column D- select Column B- go to Data- Text to Columns- Select Delimited- check Other and enter _- Finish enter image description here

You must log in to answer this question.

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