convert Unhighlighted data into yellow highlighted data format.
- Thread starter Shabbo
- Start date
Well, I would have said "yes, using data > text to column, and choosing space and S as delimiters".
However, it seems that you have several cells which contain newline characters (\n\r aka crlf aka chr(13)chr(10)).
Except if someone knows a way to remove them with search and replace, I would go with an additional column with the following formula:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)," "),CHAR(10)," "))
Then I would copy and paste values (not formulas) in column A.
And finally, use Data > Text to Columns > Delimited > Next.
Select Space and Other: S as delimiters, and treat consecutive delimiters as one > Next.
Decide which columns you want to keep, and which to skip. Here you may need to scroll down, as you don't have the same number of data in each cell. So the additional columns may not be visible in the first rows.
Then click finish, and you are done. You will just have to add the column titles.
However, it seems that you have several cells which contain newline characters (\n\r aka crlf aka chr(13)chr(10)).
Except if someone knows a way to remove them with search and replace, I would go with an additional column with the following formula:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)," "),CHAR(10)," "))
Then I would copy and paste values (not formulas) in column A.
And finally, use Data > Text to Columns > Delimited > Next.
Select Space and Other: S as delimiters, and treat consecutive delimiters as one > Next.
Decide which columns you want to keep, and which to skip. Here you may need to scroll down, as you don't have the same number of data in each cell. So the additional columns may not be visible in the first rows.
Then click finish, and you are done. You will just have to add the column titles.
John Jairo V
Well-Known Member
Hi to all!
Directly with one formula that you can drag into right and down, check the file. Blessings!
Directly with one formula that you can drag into right and down, check the file. Blessings!