Remove Char(0160) and replace with indent level

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,251
Office Version
  1. 2016
Platform
  1. Windows
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
AB
1First Level0
2 Second Level6
3 Third Level12
4 Fourth Level24
5 Fifth Level32
6 Fifth Level32
7 Sixth Level40
8 Sixth Level40
9 Sixth Level40
10 Sixth Level40
11 Sixth Level40
12 Sixth Level40
13 Sixth Level40
14 Sixth Level40
15 Sixth Level40
16 Fifth Level32
17 Sixth Level40
18 Sixth Level40
19 Sixth Level40
20 Sixth Level40
21 Sixth Level40
22 Sixth Level40
23 Sixth Level40
Sheet1 (2)
Cell Formulas
RangeFormula
B1:B23B1=LEN(A1)-LEN(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,251
Office Version
  1. 2016
Platform
  1. Windows
This is great. Thanks Fluff.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,956
Messages
5,678,766
Members
419,783
Latest member
yazan

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