Remove leading non-displayable characters from cell

[AjohnWoods]

When I retrieve data from SQL table, the cell content always has leading non-displayable characters, which I think are carriage returns, that need to be removed

For example:

here is the same using a text editor that shows the cell contents in HEX values

It is the leading 0D 0A 0D 0A 0D 0A that I want to remove
using CLEAN is no good, as that also removes the 0D 0A from the middle of the cell also, I need to keep those
could care less about those that trail the text block

As the spreadsheet is generated by another program, I need to avoid using VBA or macros.
A formula is the optimal solution here.


(Office 365, Windows 10)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(split,MID(A2,SEQUENCE(LEN(A2)),1),MID(A2,MIN(IF(AND(split<>CHAR(10),split<>CHAR(13)),SEQUENCE(LEN(A2)),"")),LEN(A2)))

 

[Fluff]

Oops, it should be

=LET(split,MID(A2,SEQUENCE(LEN(A2)),1),MID(A2,MIN(IF((split<>CHAR(10))*(split<>CHAR(13)),SEQUENCE(LEN(A2)),"")),LEN(A2)))

 

[AjohnWoods]

Thanks Fluff, after also applying wrap text to the cell it works.
Problem solved!

 

[Fluff]

You're welcome & thanks for the feedback.