Finding largest instance of consecutive prefix spaces in a worksheet

[lmoseley7]

I'm copying a PeopleSoft department tree into Excel. For those unfamiliar, it is a grouping of departments similar to a file structure where certain departments roll up into other nodes (folders) that all eventually roll up to the ultimate parent node (folder). When pasting this into Excel, the levels I see in PeopleSoft are now denoted by preceeding spaces to indicate the former roll up structure. In the past I have found the largest number of preceeding spaces and starting with that number, used find and replace to replace the spaces with commas and then used text to columns to create the separation between levels through columns. I'd like to automate this with a macro.

What I envision is searching for the largest consecutive number of spaces and then working from that number down to 2, find and replace each with commas. Alternatively, I guess we could start with a number, say 10 for instance, and test for 10 spaces, then 9, etc. until we find the number and then use that to work through the find and replace code. I'm sure I could write something very clunky that would ultimately work, but I was hoping someone here might be able to offer a suggestion of how to make this happen in an efficient manner, both from a code and a coding perspective.

Thanks in advance for the help.

 

[Rick Rothstein]

I'm copying a PeopleSoft department tree into Excel. For those unfamiliar, it is a grouping of departments similar to a file structure where certain departments roll up into other nodes (folders) that all eventually roll up to the ultimate parent node (folder). When pasting this into Excel, the levels I see in PeopleSoft are now denoted by preceeding spaces to indicate the former roll up structure. In the past I have found the largest number of preceeding spaces and starting with that number, used find and replace to replace the spaces with commas and then used text to columns to create the separation between levels through columns. I'd like to automate this with a macro.

What I envision is searching for the largest consecutive number of spaces and then working from that number down to 2, find and replace each with commas. Alternatively, I guess we could start with a number, say 10 for instance, and test for 10 spaces, then 9, etc. until we find the number and then use that to work through the find and replace code. I'm sure I could write something very clunky that would ultimately work, but I was hoping someone here might be able to offer a suggestion of how to make this happen in an efficient manner, both from a code and a coding perspective.

See if you can make use of this. You can get a count of the number leading blanks in a text string like this...

LeadingBlankCount = Len(TextString) - Len(LTrim(TextString))

Note that there is an "L" in front of the word "Trim"... that "L" is very important, so make sure you include it.

 

[lmoseley7]

That should be helpful. Now I just have to figure the rest of the code. Thanks!

 

[lmoseley7]

Okay, LTRIM doesn't exist in my version of Excel 2013. I was testing this in a formula within the worksheet prior to trying to code it in VBA. Also, assuming LTRIM exists in VBA, I could use some help figuring out the code to test a column of data for the highest number of leading spaces.

 

[lmoseley7]

Ok a quick Google search shows LTRIM is a VBA function. I still would appreciate some ideas for testing the column of data and keeping up with the maximum number. I'm thinking I need to test each cell in the column for the number of leading spaces, compare that to a variable set as the number of leading spaces of the previous maximum amount, and if it is greater, replace the variable, if not, keep the former number. (It's even complicated in english, much less VBA code.) After I find the maximum number of leading spaces, I need to replace those spaces with commas, stepping down a number until I reach the minimum. That part I can probably handle. If anyone would be willing to get me started on testing a column of unknown size for the maximum number of leading spaces, I would appreciate it.

 

[Rick Rothstein]

Ok a quick Google search shows LTRIM is a VBA function. I still would appreciate some ideas for testing the column of data and keeping up with the maximum number. I'm thinking I need to test each cell in the column for the number of leading spaces, compare that to a variable set as the number of leading spaces of the previous maximum amount, and if it is greater, replace the variable, if not, keep the former number. (It's even complicated in english, much less VBA code.) After I find the maximum number of leading spaces, I need to replace those spaces with commas, stepping down a number until I reach the minimum. That part I can probably handle. If anyone would be willing to get me started on testing a column of unknown size for the maximum number of leading spaces, I would appreciate it.

The following macro will replace all leading spaces on the active worksheet with commas...

Sub ConvertLeadingSpacesToCommas()
  Dim R As Long, C As Long, LeadSpaces As Long, Arr As Variant, UR As String
  UR = ActiveSheet.UsedRange.Address
  Arr = Range(UR)
  For R = 1 To UBound(Arr)
    For C = 1 To UBound(Arr, 2)
      If Len(Arr(R, C)) Then
        LeadSpaces = Len(Arr(R, C)) - Len(LTrim(Arr(R, C)))
        Arr(R, C) = String(LeadSpaces, ",") & Mid(Arr(R, C), LeadSpaces + 1)
      End If
    Next
  Next
  Range(UR) = Arr
End Sub

 

[lmoseley7]

Well, I would have never come up with that. In fact, I'm still trying to digest exactly what it says. Much more efficient than testing a range for a max and then systematically finding and replacing strings. I'm extremely humbled by your coding ability. What's sad is that this is probably elementary for you!

If you would allow, I'd like to ask a few questions and present my understanding of your code so that I can learn from this example.

I see first you are setting the used range. If I only wanted to look at column A, how could I change the code?

Next you set "Arr" equal to the range of "UR". Two lines down I'm not sure what is happening. The code is: For C = 1 To UBound(Arr, 2). I understand that you are cycling through the possible column numbers based upon the Arr range. I understand that the "2" integer in the UBound function returns the second dimension. I assume the second dimension is known to be the column in a range?

Last question, what is the purpose of the line "If Len(Arr(R, C)) Then"? If statements return true or false, but there is no apparent test here because there is no =, <, >, etc. I guess this is just testing if there is anything in cell A1? Basically just giving the code a place to start?

Thanks for all your help and the knowledge you have shared.

 

[Rick Rothstein]

If you would allow, I'd like to ask a few questions and present my understanding of your code so that I can learn from this example.

Of course you can ask... those posting code and/or formulas in this forum should be happy to try and explain the logic behind their postings.

I see first you are setting the used range. If I only wanted to look at column A, how could I change the code?

Since you only want to process one column, I would change my code to this instead of what I posted earlier (note that I commented every line for you)...

Sub ConvertLeadingSpacesToCommas()
  Dim R As Long, C As Long, LastRow As Long, LeadSpaces As Long, Arr As Variant
  [COLOR=#008000]'  Find last row in Column A that has data in it[/COLOR]
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 [COLOR=#008000] '  Assign the range of data to the array Arr so we can work with the data in memory (faster)
  '  rather than going back and forth to the worksheet to get the data one at a time (slower)
  '  Note: The array will be two dimensional even though one column of cells is being assigned to it[/COLOR]
  Arr = Range("A1:A" & LastRow)
  [COLOR=#008000]'  Loop through each element of the array[/COLOR]
  For R = 1 To LastRow
    [COLOR=#008000]'  Only process an array element if it actually has data in it, that is, skip it if empty[/COLOR]
    If Len(Arr(R, 1)) Then
      [COLOR=#008000]'  Find out how many leading spaces there are[/COLOR]
      LeadSpaces = Len(Arr(R, 1)) - Len(LTrim(Arr(R, 1)))
      [COLOR=#008000]'  Create a string of commas equal in length to the number of leading spaces and concatenate
      '  the text after the last leading space onto it, then change the array element to equal it[/COLOR]
      Arr(R, 1) = String(LeadSpaces, ",") & LTrim(Arr(R, 1))
    End If
  Next
  [COLOR=#008000]'  Assign the modified array back to the cells the original array came from[/COLOR]
  Range("A1").Resize(LastRow) = Arr
End Sub

Next you set "Arr" equal to the range of "UR". Two lines down I'm not sure what is happening. The code is: For C = 1 To UBound(Arr, 2). I understand that you are cycling through the possible column numbers based upon the Arr range. I understand that the "2" integer in the UBound function returns the second dimension. I assume the second dimension is known to be the column in a range?

Yes, assigning a range of cells to a Variant variable creates a two-dimensional array (even if that range consists of only one row or one column of data).

Last question, what is the purpose of the line "If Len(Arr(R, C)) Then"? If statements return true or false, but there is no apparent test here because there is no =, <, >, etc. I guess this is just testing if there is anything in cell A1? Basically just giving the code a place to start?

In VB (and sort of in Excel as well, although the typing for TRUE is a little stronger when explicitly specified), the two Boolean values are not really TRUE and FALSE, rather, they are FALSE and NOT FALSE... FALSE is equivalent to 0, NOT FALSE (what TRUE is really) is any number that is not 0, so Len(Arr(R,C)) will be equivalent to True (Not False) if it is a non-zero number (meaning the text has length) and False if it is 0 (meaning there is no text).

 

[lmoseley7]

You've been more than gracious and I appreciate it. Your changes brought up one additional question though. I didn't understand the last line and comment:
' Assign the modified array back to the cells the original array came from
Range("A1").Resize(LastRow) = Arr

I looked up the Resize function and I think I understand what it is used for, but I'm not sure what we are using it for here. I guess this is equivalent to the line in your original code that read "Range (UR) = Arr" after earlier in the code having "Arr = Range (UR). I'm just not sure what the purpose of this code is.

 

[Rick Rothstein]

You've been more than gracious and I appreciate it. Your changes brought up one additional question though. I didn't understand the last line and comment:
' Assign the modified array back to the cells the original array came from
Range("A1").Resize(LastRow) = Arr

I looked up the Resize function and I think I understand what it is used for, but I'm not sure what we are using it for here. I guess this is equivalent to the line in your original code that read "Range (UR) = Arr" after earlier in the code having "Arr = Range (UR). I'm just not sure what the purpose of this code is.

VB can access data stored in memory (which is where an array is stored) much faster than if it tries to retrieve data from a single cell on a worksheet. So the first assignment takes all the data we want to work with and puts it into an array (VB can do that very fast), then we modify the array elements, but that does not modify the cells where the data came from, rather it just modifies the copy of that data we placed in the array, so to see those changes, we put the array's values back into the cells of the worksheet.