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 ..)

 

[Peter_SSs]

Could we have some varied sample data and expected results with XL2BB?

Does this have to be with vba or would a formula solution be acceptable?

 

[Siddharth Rout]

You can easily achieve this using formulas.

Here is the Logic

1. Find the first "*". You can do that using the formula SEARCH()

=SEARCH("~*",A1,1)

2. Find then 2nd "*". You can do that using the formula

=SEARCH("~*",A2,SEARCH("~*",A2,1)+1)

3. To find if the character after the 2nd "*" is space or not you can use MID() with the above.

=IF(MID(A2,SEARCH("~*",A2,SEARCH("~*",A2,1)+1)+1,1)=" ","There is space","There is no space")

Here is an example

PS: I am not doing any error handling. I am sure you can take care of it?

 

[Siddharth Rout]

Alternative 2

After the first "*", do not search for "*" but search for "* ". Notice the SPACE after "*"?

Here is the Logic

1. Find the first "*". You can do that using the formula SEARCH()

=SEARCH("~*",A1,1)

2. Find the asterix with SPACE ("* "). You can do that using the formula

=SEARCH("~* ",A2,SEARCH("~*",A2,1)+1)

3. Final formula

=IFERROR(IF(SEARCH("~* ",A1,SEARCH("~*",A1,1)+1)>0,"There is space","There is no space"),"There is no space")

 

[Peter_SSs]

Without further samples, perhaps just this?

22 08 23.xlsm
	A	B
1	*E* Sometext	*E* Sometext
2	*TOP S+E*sometext	*TOP S+E* sometext
Space after asterisk

Cell Formulas
Range	Formula
B1:B2	B1	=TRIM(SUBSTITUTE(A1,"*","* ",2))

 

[Siddharth Rout]

Without further samples, perhaps just this?

22 08 23.xlsm
	A	B
1	*E* Sometext	*E* Sometext
2	*TOP S+E*sometext	*TOP S+E* sometext
Space after asterisk

Cell Formulas
Range	Formula
B1:B2	B1	=TRIM(SUBSTITUTE(A1,"*","* ",2))

Very nice Peter. Much simpler solution than mine!

 

[silentwolf]

Hi thanks for your replies!

I need to add a space if there is no space and if there is a space just leave it like the text is.

I would prefer it in vba to be able to use it in future as there is every few month new prices and I need to update those.

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

Left is the Data and right is how I like to have it in the hole Column )


So all of the "Text" needs to have a space after the Second "*"

 

[Siddharth Rout]

If you want VBA then you can simply use Peter's formula and adapt it to VBA?

 

[silentwolf]

Hi Peter,

Yes that works in a formula ! Thanks just had to updated it to a german version of Excel but it is all good!

Still it would be nice to have that in a function so i could used it to automatically update the received Sheets in future.

Cheers

 

[Peter_SSs]

Assuming that data is in column A, starting in row 2, try this with a copy of your data. (It over-writes the original data)

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