Substituting SPACE, CF and LF characters with a TAB character?

[slipstream90]

Fellow Forum Members,
I have text string data that has unwanted Carriage Returns (CF) and Line Feeds (LF) I need substituted with a TAB. I also need all Single Blank Spaces substituted with a TAB. What is making this complicated is how I am dealing with developing a formula that involves substituting invisible characters with a different invisible character. Below in BOLD is a snippet of the sample data I am working with:

TrnfrCode="02" subCode="8"(CF)(LF)
KHTVariant="04" ASPCode="5"

Can someone please help me develop an Excel 365 formula that can substitute a single blank [SPACE] with a TAB and also substitute (CF)(LF) invisible characters with a TAB?

The link below provides a list of Character Codes used in Excel. The problem I am having is constructing the correct formula syntax using the Character Codes bulleted below so the formula correctly performs the needed substitutions to the sample text shown above in BOLD:

• TAB Character = CHAR(9)
• SPACE Character = CHAR(32)
• NEW LINE Character = CHAR(10) This one is a problem because it is not not equate to (CF)(LF) characters.

MS Excel Formulas: CHAR/CODE - Text Functions

Any opinions welcome and thank you for your time with matter.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",CHAR(9)),CHAR(10),CHAR(9)),CHAR(13),CHAR(9))

 

[slipstream90]

Hi & welcome to MrExcel.
How about
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",CHAR(9)),CHAR(10),CHAR(9)),CHAR(13),CHAR(9))

Fluff,
Thank you for your post. Your formula correctly inserts the TAB characters where they need to be. However, I am confused why your formula does not split the text string into Cells A2, B2, C2, D2, E2, F2? Shouldn't the TAB character your formula adds to the text string automatically split up the text string into separate columns? Maybe I am wrong to think that TAB characters magically split the data so it ends up populating multiple columns horizontally. Can your formula be modified to handle automatic text string splitting across multiple columns? I don't need the text string to only contain itself within A2 and not spread itself across multiple columns. Any opinions welcome.

 

[Fluff]

The tab character does absolutely nothing.
Can you please explain exactly what you need, not what you think you want.
Also please post some sample date, along with expected results, using the XL2BB add-in. Thanks