Add Space after second "*" if there is no space afterwards

[silentwolf]

Hi,
I asked that same question on my previous post but thought it would be better to start a new post for this.

I got in one column "Names" they can start with an asterix "*p* Sometext"

this can very from "*p* sometext
to *E* Sometext

or other text with *different length* somthing like "*TOP S+E* sometext"

So I like to find the second "*" from left and add a space if there is no space.

"*TOP S+E*textwithoutSpace after the asterisk
of course I need to keep the remaining Text.

So I guess I should use instr Function to find the second "*" but then how to I find if there is a space or no space after the asterisk and how to add one if there is non ??

Hope that is clear what I mean?

Cheers ..)

 

[silentwolf]

Hi thanks Peter,

I created this function is that also ok?

Function UpdateArticle(ByVal strT As String) As String
    UpdateArticle = Trim(WorksheetFunction.Substitute(strT, "*", "* ", 2))
End Function

it seams to work but I guess it is not simpler then yours or using the formula in the first place...

@Peter_SSs
your last example updates all with the same value.

in my case I have in the first column "Dichtungsring f. Ansaugstück"
then it updates each Row with that value ..(

 

[silentwolf]

Hi again,

this is how I ended up with and it seams to work fine.

what do you guys think of that?

Function UpdateArticle(ByVal strT As String) As String
    UpdateArticle = Trim(WorksheetFunction.Substitute(strT, "*", "* ", 2))
End Function

Sub UpdateNew()
    Dim Spaltentext() As Variant
    Dim i As Integer

    Application.ScreenUpdating = False

    Spaltentext = Range("B2", Range("B1").End(xlDown))
    For i = LBound(Spaltentext, 1) To UBound(Spaltentext, 1)
        Spaltentext(i, 1) = UpdateArticle(Spaltentext(i, 1))
    Next i
    Range("B2").Resize(UBound(Spaltentext, 1), 1) = Spaltentext

    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

your last example updates all with the same value.

Did you adjust the code? My assumption was that data was in column A. If it is in column B then the code would be

Sub SpaceAfterSecondAsterisk()
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate("trim(substitute(" & .Address & ",""*"",""* "",2))")
  End With
End Sub

Here is my sample data before the code

silentwolf.xlsm
	B
2	*P* BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
3	*P*BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
4	*E*Abdeckring VS-Düse
5	*P*Heizkörper 1 1/2" 4500 W 230/400 V -Restbestand-nicht mehr lieferbar-
6	*TOP-S+E*Fernbedienung Funk Reviva
Sheet3

.. and after the above code:

silentwolf.xlsm
	B
2	*P* BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
3	*P* BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
4	*E* Abdeckring VS-Düse
5	*P* Heizkörper 1 1/2" 4500 W 230/400 V -Restbestand-nicht mehr lieferbar-
6	*TOP-S+E* Fernbedienung Funk Reviva
Sheet3

this is how I ended up with and it seams to work fine.

Are you sure?
I think that if you check carefully, any cells that already had a space after the second asterisk will now have 2 spaces after the second asterisk.

Your function would need this change

Function UpdateArticle(ByVal strT As String) As String
    UpdateArticle = Application.Trim(WorksheetFunction.Substitute(strT, "*", "* ", 2))
End Function

 

[silentwolf]

Hi Peter,

Yes I did adjust your code to suit.
But even if I try your code it does give me in all rows what is in the first Cell.

Peter not all of the cells are starting with "*X*"

So in "B2" is in my worksheet "Dichtungsring f. Ansaugstück"
After running your code this value is in all of the Cells in that column.

Thanks for the modified code!

Function UpdateArticle(ByVal strT As String) As String
    UpdateArticle = Application.Trim(WorksheetFunction.Substitute(strT, "*", "* ", 2))
End Function

And I checked if I run my "Code" it does not add another extra space into it..

 

[Peter_SSs]

Peter not all of the cells are starting with "*X*"

That is new information that we did not have before. All of your earlier samples started with an asterisk and also had a second asterisk.
If you would like any further investigation, then please give us some more representative sample data and the expected results.

And I checked if I run my "Code" it does not add another extra space into it..

Oh well, it did for me.

Before

silentwolf.xlsm
	B
1	Hdr
2	*P* BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
3	*P*BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
4	*E*Abdeckring VS-Düse
5	*P*Heizkörper 1 1/2" 4500 W 230/400 V -Restbestand-nicht mehr lieferbar-
6	*TOP-S+E*Fernbedienung Funk Reviva
Sheet4

After, using your original function with Trim rather than Application.Trim (Cell B2 has two spaces after the second asterisk)
The vba Trim function only trims at the very left and right of the text, not in the middle.

silentwolf.xlsm
	B
1	Hdr
2	*P* BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
3	*P* BGR. Drucktasterbedienfeld 6-Punkte incl. Steuerung Ergo+ 2015
4	*E* Abdeckring VS-Düse
5	*P* Heizkörper 1 1/2" 4500 W 230/400 V -Restbestand-nicht mehr lieferbar-
6	*TOP-S+E* Fernbedienung Funk Reviva
Sheet4

 

[silentwolf]

Hi,

That is new information that we did not have before. All of your earlier samples started with an asterisk and also had a second asterisk.
If you would like any further investigation, then please give us some more representative sample data and the expected results.

Sorry did not mean to give not enough information but it was relevant to get the spaces correct if there is text starting with an asterisk.

*E* Dampfdüse 2013 für DB mit integrierte Kondensatschale, Farbe: Anthrazit	Dampfdüse
*KD* Y-Stück	Y-Stück
*P* Schlauchklemme 19.2	Schlauchklemme
*TOP-S+E* Steuerung Sie UCP1+Siefu incl. Gehäuse u. Verkabelung	Steuerung
1-Ohr-Klemme mit Einlagering Referenz-Nr. 06.1GER	Klemme
2/2 Weg.Sitzventil NC 1/2 ' * (Tra)	well here it would show Weg.Sitzventil I guess.

So the reason behind all this is just to get some better filter options when this data is updated.

After splitting it would be something like that

Text	=SplitWords(B2;1)	=SplitWords(B2;2)	=SplitWords(B2;3)
*E* Dampfdüse 2013 für DB mit integrierte Kondensatschale, Farbe: Anthrazit	*E*	Dampfdüse	2013
*E* Klemmring zu Venturi Düse	*E*	Klemmring	zu
Abdeckkappe außen re. E-GLP4	Abdeckkappe	außen	re.

above is after I split the data then I would get in one column

Function SplitWords(varText As String, n As Long) As String
    SplitWords = Split(varText & Space(n), " ")(n - 1)
End Function

Bezeichnung	FirstWord	SecondWord	ThirdWord
Dichtungsring f. Ansaugstück	Dichtungsring	f.	Ansaugstück
DB SensePerience Rechteck 1400 rechts/Vorwand - Frontscheibe rechts	DB	SensePerience	Rechteck
Sonderschraube für Fostersitz	Sonderschraube	für	Fostersitz
Längsteil 500mm mit Sitz	Längsteil	500mm	mit
Längsteil 500mm mit Sitz	Längsteil	500mm	mit
Kuppeldach 2x2 abger.Ecken	Kuppeldach	2x2	abger.Ecken
Kuppeldach 1 x 2 m	Kuppeldach	1	x
Rosette für Power LED - Leuchte, verchromt	Rosette	für	Power
*P* LED 12V weiss	*P*	LED	12V
LED 12V - Licht in weiss	LED	12V	-
*P* LED 12V - Licht in weiss	*P*	LED	12V
*P* BGR. Beleuchtung Eck - kmpl	*P*	BGR.	Beleuchtung
6 adriges Verlängerungskabel LED RGB 2 m lang	6	adriges	Verlängerungskabel
ABS Halter für Aluschürze	ABS	Halter	für
Verteilerstück kpl. 10 Abgänge mit Kugel	Verteilerstück	kpl.	10
Verteilerstück W kpl. 10 Abgänge	Verteilerstück	W	kpl.
Winkel für Düse Typ PO 1-seitig, für Schlauch, Durchm. 10 mm	Winkel	für	Düse

After, using your original function with Trim rather than Application.Trim (Cell B2 has two spaces after the second asterisk)
The vba Trim function only trims at the very left and right of the text, not in the middle.

ok thanks I did change that to application.trim

So that is how I use it at present and it is to be able to get "Categories" out of the Text for better filter as it is difficult to find otherwise "Articles"

Hope this is now better understandable?

Not sure what else I should provide?

And not sure if that is now the best way of doing it the way I got it set up.