Remove Char(0160) and replace with indent level

[FryGirl]

Here is some data downloaded from the web. At the front is the Char(0160). I would like to remove the Char(0160) and replace with nothing, but then indent the cell based on the level. As you can see with the data below, I was messing around with the cleaning dirty data formula, but wasn't sure how to get the indent level desired without some overcomplicated way. Can this be done directly in column A without the use of a formula? Also, it turns out some of these rows have extra spaces at the end of the string.

level 1 -- indent 0
level 2 -- indent 1
level 3 -- indent 2
and so on

List.xlsx
	A	B
1	First Level	0
2	Second Level	6
3	Third Level	12
4	Fourth Level	24
5	Fifth Level	32
6	Fifth Level	32
7	Sixth Level	40
8	Sixth Level	40
9	Sixth Level	40
10	Sixth Level	40
11	Sixth Level	40
12	Sixth Level	40
13	Sixth Level	40
14	Sixth Level	40
15	Sixth Level	40
16	Fifth Level	32
17	Sixth Level	40
18	Sixth Level	40
19	Sixth Level	40
20	Sixth Level	40
21	Sixth Level	40
22	Sixth Level	40
23	Sixth Level	40
Sheet1 (2)

Cell Formulas
Range	Formula
B1:B23	B1	=LEN(A1)-LEN(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

 

[Fluff]

How about

Sub FryGirl()
   Dim Cl As Range
   Dim x As Variant
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      x = Evaluate("LEN(" & Cl.Address & ")-LEN(SUBSTITUTE(" & Cl.Address & ",CHAR(160),""""))")
      If Not IsError(x) Then
         Cl.Value = Trim(Replace(Cl.Value, Chr(160), ""))
         Cl.IndentLevel = x / 6
      End If
   Next Cl
End Sub

 

[FryGirl]

This is great. Thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.