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

slipstream90

New Member
Joined
Jun 15, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,581
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",CHAR(9)),CHAR(10),CHAR(9)),CHAR(13),CHAR(9))
 

slipstream90

New Member
Joined
Jun 15, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,581
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,678
Messages
5,626,227
Members
416,169
Latest member
DROP_DATABASE_MrExel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top