• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert Unhighlighted data into yellow highlighted data format.

Shabbo

Member
Dear Sir,

Is it possible to convert Unhighlighted data into yellow highlighted data format.
 

Attachments

  • Entry.xls
    31 KB · Views: 9
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.
 
Another shorter formula proposal.

In B2, formula copy across and down :

=TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A2,"S"," S"))," ",REPT(" ",300)),COLUMNS($A1:A1)*600,300))

Regards
Bosco
 

Attachments

  • Entry(1).xls
    133.5 KB · Views: 6
Back
Top