Removing characters in data set

[cubiclemonkey]

Good Afternoon,

I have a few data sets that I am downloading from SAP into excel and the data sets have labels in the subtotals in column B that aren't uniform. For example one data set will have 3 asterisks with spaces preceding the label like "*** Labour Costs" whereas another data set could have 4 or 5 asterisks. Is there a macro that can go through a set of tabs and delete out the asterisks for each data label irrespective of the number along with the spaces and just leave the label?

Thanks in advance.
Ahmed

 

[Micron]

You can loop over the sheets or not?
You can replace * with vba Replace function. An example would be

Replace("**These Words**", "*", "")

delete out the asterisks for each data label irrespective of the number along with the spaces

Not clear what that means. Delete the spaces also?

 

[cubiclemonkey]

Sorry for not being clear. For example in tab one there will be 3 asterisks followed by 5 spaces then the label name (i.e. *** Labour). Another tab will have 4 asterisks followed by 5 spaces and then the label name (i.e. **** Labour). Another will have 2 asterisks and so on. So I was trying to get a macro that removes all the asterisks regardless of the number as well as the space so that I just have the label name like Labour.

 

[Rick Rothstein]

Maybe something like this...

CleanText = Trim(Mid(CellText, InStr(CellText, "* ") + 1))

 

[cubiclemonkey]

Thanks Rick let me try that.

 

[cubiclemonkey]

Thanks Rick let me try that.

The formula above didn't work so I came up with the following, however, is there a way to make it dynamic so that if there are any combinations of asterisks and spaces it will just remove it from the selected tabs?

Sub asterisks()

Dim ws As Worksheet
Dim sheetArray As Variant

'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets

'Loop through each selected worksheet
For Each ws In sheetArray

'Select the worksheet
ws.Select

Columns("B:B").Select
Selection.Replace What:="** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="*** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="*** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="**** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="***** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="****** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next ws

'Reselect the sheets
sheetArray.Select

End Sub

 

[Rick Rothstein]

The formula above didn't work so I came up with the following, however, is there a way to make it dynamic so that if there are any combinations of asterisks and spaces it will just remove it from the selected tabs?

What I posted wasn't a formula... it was a VBA code line. You were supposed to replace the words (both of them) with either the variable or cell reference containing your text or the actual quoted text itself. If your text is as was described in your first message, I can see no way why it would not have worked. Can you show me the exact text you tried to use it on which produced the error you saw? Also, what was the error message (number and description)?

 

[cubiclemonkey]

Ah silly me, thanks for the clarification. An example of the text is as follows:

This tab has 6 spaces between the asterisk and the row label:
** LABOUR COSTS
** OTHER OPERATING EXPENSES
*** TOTAL EXPENDITURES
** MISCELLANEOUS REVENUE
*** REVENUES
**** NET OF ALLOCATIONS
***** NET OF ALLOCATIONS

Another tab would have 7 spaces as shown below:
*** LABOUR COSTS
*** OTHER OPERATING EXPENSES
**** TOTAL EXPENDITURES
*** ONTARIO & CANADA GRANTS
*** REVENUES FROM 3rd PARTIES
*** USER FEES
*** OTHER
*** MISCELLANEOUS REVENUE
**** REVENUES
**** ALLOCATIONS
***** NET OF ALLOCATIONS
****** NET OF ALLOCATIONS

And another would have 9 spaces and so on.

As for using your suggestion, I couldn't figure out how to use it in the loop and so replaced it with what I have shown above.

 

[Micron]

I would think this should work, but I can't even get an asterisk into a tab name. How is it that either of you can?

Sub tabNames()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
     sht.Name = Replace(Replace(sht.Name, " ", ""), "*", "")
Next

End Sub

I don't see all of these spaces you're posting about. If forum is removing them on you, then use code tags to maintain the spaces.