How to use Find/Replace for finding/replacing Tabulator characters in the Excel 365?

Question

How to use Find/Replace for finding/replacing Tabulator characters in the Excel 365?

***Moved From fi-fi***

Answer 1

Hey, Yarney.

I'm sorry my function wasn't written clearly and it gave you a misunderstanding, I'm improving my program.

Here are two options, you can choose the one that suits your needs.

1. Use your Replace. can only replace onece, cannot replace all.

The Replace function can also achieve this effect, this function must specify the location of the spreadsheet device.

=REPLACE(B2;FIND(CHAR(9);B2);1;" ") 

This function can only replace the tab character, can not replace all.

The FIND function is used to find the location of the Tabulator character.

2. Use the SUBSTITUED function. =SUBSTITUTE(B4;CHAR(9);" ")

This action replaces all Tabulator characters.

Let me know if this is contrary to what you need.

Best regards.

Pedro | Microsoft Community Support Specialist

Answer 2

Hi Yarney.

Thank you for using Microsoft products and publishing in the community.

I understand that you have encountered an Excel problem.

Before I answer your question, I need to know some information so I can better help you with your problem.

Excel does not explicitly indicate which characters are column characters

What do the tab characters you mention look like?

How do you add tabs to Excel?

If it's OK with you, could you share the contents of the file with us? You can send me the file by private message.

Here are some methods for your reference:

1.If you have ever thought about replacing tabs, here is a formula to replace them.

=REPLACE(B10; 5,1;" ") .

The function is to replace all tabs with spaces.

CHAR (9) is represented as a tab character that matches all Tab characters.

2.Do the brackets you mention belong to this type:

─━│┃╌╍╎╏┄┅┆┇┈┉┊┋┌┍┎┏┐┑┒┓└�┕┖┗┘┙┚┛├┝┞┟┠┡┢┣┤┥┦┧┨┩┪┫┬┭┮┯┰┱┲┳┴┵┶┷┸┹┺┻┼┽┾┿╀╁╂╃╄╅╆╇╈╉╊╋

If the signs you need are not available, you can resubmit them to us.

You can open the search menu by pressing Ctrl+F, add the selected character to the search box and replace or search for it:

Let me know if this conflicts with what you need.

Best regards.

Peter | Microsoft Community Support Specialist

Answer 3

Moi,

Thanks. The option 1 looks promising, but REPLACE function in my Excel needs more arguments than three above, as it is: REPLACE, REPLACEB functions - Microsoft Support

The char that I am trying to replace is Windows-1252 character set char #09.

Sorry, I do not want to send my file due to privacy reasons as it contains customer data.

PS. the Finnish translation is strange, the original English text might be better.

Answer 4

Thanks, The SUBSTITUTE does what I need.

BR, Yarney