Hiding Underscore

[rajltd]

I have more than 20 worksheets in some workbook, and the only way I can differentiate them is through writting the name of the worksheet on the top as header by "&A". But most of the worksheets have an underscore, eg. ABC_Accessories. And this underscore wont look good in the title name of the page. Can I write any code or is there any utility in Excel to hide the Underscore.

Any help would be appreciated.

Many Thanks,

Raj

 

[SQLBI]

Raj,

Try this formula in your worksheet.

=LEFT(A1,FIND("_",A1)-1)&" "&RIGHT(A1,LEN(A1)-FIND("_",A1))

Assuming your ABC_Accessories is in cell A1, this will return ABC Accesories. Otherwise you'll just need to change the A1 cell ref to suit your workbook.

Hope this helps.

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[rajltd]

Thanks very much Leigh.

But a few problems. First, I want this thing to happen in the header and not in any cell, so the cell reference is a concern. Second thing is, will it work if its ABC_XYZ_Accessories, kind of refernece. Third, Where shall I write this code?? I mean at what place, will it be in a cell or some place in the worksheet(where shall i write the formula??)

Thanks for help again.

Raj

 

[SQLBI]

Raj,

1. When you say header, do you mean in the 1st row of your worksheet or in the Header/Footer of the printed page. If it's the Header/Footer this won't work.

2. This particular formula will only work with a single '_' in a particular string.

Another way around this would be Edit > Replace - Find "_" Replace with " " (single space.)

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[rajltd]

Yeah, its in header of header/footer. And it can be more than one '_' in a string.