How to increase indent level of exported data?

[mpolo954]

Good day to you all,
I am trying to learn how I may increase the indent level of exported data by a multiple of 2 or 3 depending on which looks better.

So that,
If current indentation level is 0: 2*0=0. The result would be the same as the starting point, no indentation.
If current indent level is 1: 2*1=2 so the result would increase the starting indent level from 1 to 2.
If current indent level is 2: 2*2=4 so the result would increase the starting indent level from 2 to 4.
and so on...

Any help on this matter would be greatly appreciated, I am attaching a worksheet for reference, the part number column is the data set that I am wanting to increase the indent level by a 2x or 3x multiplier so that the indent is more obvious .

 

[Marcilio]

mpolo954, Good evening.

Maybe I'm missing something but this indentation in column C did you manage to put the spaces manually, for demonstration?

If so a have a modest suggestion.

a) Select B column

b) FIND-REPLACE: space by nothing. Them all of texts will left-aligned equal.

c) Cell C2 --> =SUBSTITUTE(C2; C2; REPT(" ";B2-1) & C2)
....... I used a group of six spaces with REPT function.

Is this what you want?

I hope it helps.

 

[vletm]

mpolo954
If You're writing about Indent ... then
There are two buttons - check below snapshot (above text Increase Indent)

other way would be to use VBA.
... and this is an Ask an Excel Question -question.

 

[p45cal]

other way would be to use VBA.

Select cells in column C that you want indenting then run this:

Sub blah()
For Each cll In Selection.Cells
  x = Len(cll.Offset(, -2)) - Len(Replace(cll.Offset(, -2), ".", "")) + 1
  cll.Value = Application.Trim(cll.Value)
  cll.IndentLevel = x
Next cll
End Sub

It ignores column B, uses column A to determine the indent level, removes spaces from the cells in column C before setting the new indent level.

 

[mpolo954]

mpolo954, Good evening.

Maybe I'm missing something but this indentation in column C did you manage to put the spaces manually, for demonstration?

If so a have a modest suggestion.

a) Select B column

b) FIND-REPLACE: space by nothing. Them all of texts will left-aligned equal.

c) Cell C2 --> =SUBSTITUTE(C2; C2; REPT(" ";B2-1) & C2)
....... I used a group of six spaces with REPT function.

Is this what you want?

I hope it helps.

Hi @Marcilio,
No this is the way that the data gets exported from Solidworks (CAD program that I use), it is a parent child relationship and relates to the other issue you were able to help me with converting 1.2.1 into 3. The indent level defines a relationship between parts in an assembly. My only issue is that the default level of indentation is not enough to clearly tell this relationship at a quick glance, so I was hoping for an easy method to increase it.

 

[mpolo954]

mpolo954
If You're writing about Indent ... then
There are two buttons - check below snapshot (above text Increase Indent)
View attachment 73784
other way would be to use VBA.
... and this is an Ask an Excel Question -question.

Thank you for your reply, this would be incredibly tedious to perform manually. I am hoping to achieve this with a function or formula so that I can automate.

 

[vletm]

mpolo954
Did You read last two lines too?

 

[mpolo954]

Select cells in column C that you want indenting then run this:

Sub blah()
For Each cll In Selection.Cells
  x = Len(cll.Offset(, -2)) - Len(Replace(cll.Offset(, -2), ".", "")) + 1
  cll.Value = Application.Trim(cll.Value)
  cll.IndentLevel = x
Next cll
End Sub

It ignores column B, uses column A to determine the indent level, removes spaces from the cells in column C before setting the new indent level.

@p45cal thank you for your reply,
I find this option very interesting, considering the data in column B was modified from column A using this formula =(LEN(A2)-LEN(SUBSTITUTE(A2,".","")))+1.

I have not been able to run this script successfully,. I am sure it is user error on my end. I pasted that script into a text editor and saved as a .vbs file. Selected cells in column C, then I double click on the .vbs file and receive this error below.
View attachment 73786

 

[mpolo954]

mpolo954
Did You read last two lines too?

Hi @vletm ,
Yes I saw your comment about VBA, I am exploring this option currently. I tried to run the script supplied by @p45cal , but I am doing something wrong as I cannot get it to function correctly.

 

[p45cal]

The code is in the attached. Follow instructions on the button in the attached.

 

[mpolo954]

@p45cal Thank you so much!

I appreciate everyones feedback, this is truly a great forum

 

[Marcilio]

mpolo954, Good morning.

Will the indentation serve as a visual reference or will it have another function?

If it is for visual reference, I think identification with more contrast is feasible.

Use the formula in cell C2:

=SUBSTITUTE(C2; C2; REPT("............|"; B2-1) & C2)
....... I used a group of 12 spaces and 1 vertical bar (pipe) at end with REPT function.

This makes dependence on item levels very visible.

I hope this attempt will be useful for you.

Have a nice day.

 

[mpolo954]

Thanks again @Marcilio,
This indentation is purely for visual reference at this point. Your solution is wonderful, and very pronounced. I love this solution so much!

 

[vletm]

mpolo954
if VBA is okay
then You could test this sample too.
There is Macro which name is Run_Once ... run it once!
After that ... if You change You A-column levels those C-column Part No.'s will have needed indents.

 

[Marcilio]

mpolo954, Good afternoon.

Thanks for the feedback.

Very pleased to have helped you.