Need to remove leading spaces within cell while retaining the Numbered Bullet Points

[jack29]

Hi,


This is my first thread.

I have following the steps to reproduce, my excel sheet column contains a lot of rows like below like 100 to 300's. But the problem is there few white spaces as leading characters which I don't want.

I have tried using TRIM() and CLEAN() functions it trims everything and presents below steps in one-liner like this --> "1. Open Browser.2. Open your application3. Add your order4. Proceed with payment.5. Exit Application", which I don't want, as I need to maintain the numbering fashion as displayed while removing leading whitespaces. Attached is sample

1. Open Browser.
2. Open your application
3. Add your order
4. Proceed with payment.
5. Exit Application

Thank you.

 

[Snakehips]

Is this perhaps just a formatting issue?
Try setting the cell format to Top Align ?????

 

[jack29]

Is this perhaps just a formatting issue?
Try setting the cell format to Top Align ?????

No, I have tried this with Top and Left Align I usually always prefer, it's not removing the white space.

 

[jack29]

screenshot showing that I have already tried using top align and left justified with cells.

 

[RoryA]

Don't use CLEAN as it will remove the line feed characters. Try just TRIM and make sure to turn word wrap on in the formula cell.

 

[Snakehips]

Yes, I just twigged that for any text formula to display other than a single string, Word Wrap must be on for the formula cell.

If it is Line return that is causing the issue then maybe like below, where I have 2 leading line returns.

Book1
	A	B
18	1. agagaggatatta 2. stuf 3More stuff	1. agagaggatatta 2. stuf 3More stuff
Sheet2

Cell Formulas
Range	Formula
B18	B18	=RIGHT(A18,LEN(A18)-2)

 

[jack29]

This solves my issue. Actually to tell you I have used this formula many times before, to delete starting few characters using RIGHT and LEFT Functions. However, now I understand it serves the purpose this way as well, to delete the line return character.

Thank you, @Snakehips

 

[jack29]

Don't use CLEAN as it will remove the line feed characters. Try just TRIM and make sure to turn word wrap on in the formula cell.

what you said is correct @RoryA , Clean() doesn't serve the purpose for me in this sceanrio. Just for showing samples, I wanted to convey to everyone that I had tried using both trim and clean.

 

[jack29]

This solves my issue. Actually to tell you I have used this formula many times before, to delete starting few characters using RIGHT and LEFT Functions. However, now I understand it serves the purpose this way as well, to delete the line return character.

Thank you, @Snakehips

Just for future reference in case. I used the same formula but subtracted by zero, =RIGHT(A2,LEN(A2)-0), doing this it retained required characters, and remove leading whitespaces