Who can write this little macro??

[Dr. Logic]

Write a macro to turn all of the spaces in the text below into dashes EXCEPT for the FIRST SPACE! Leave the first space there, but every space AFTER the first space must be replaced with a DASH!

PRU P3930 3T8 U6 VWA TMA D9 SC 120

This should be an easy one for you guys.

The above text is a catalog number so it will be changing all the time and the spaces will not always be in the same place - the only thing that will stay constant is that the manufacture name (PRU) will always be on the beginning and end with a space and that space will always be the FIRST space so that space must remain to separate the manufacture from the catalog number.

Any takers?

 

[Jonmo1]

Does it have to be a macro?

Try a formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","^",1)," ","-"),"^"," ",1)


The ^ is just a random character of your choice that is least likely to actually appear in the original string.

 

[Dr. Logic]

Well, okay - your temporarily replacing the first space with a caret and then replacing all remaining spaces with the dash, and then going back and replacing the first incidence of a caret with a space.

That's would work - as long as there are no carets in the original text.

Why a macro?

I have no place in my spreadsheet to actually put the formula so i was trying to do it without entering any data into the actual spreadsheet - but I might be able to make a workaround for that .

A very nice formula with a trick in it - I may use that. Thanks

 

[Jonmo1]

A very nice formula with a trick in it - I may use that. Thanks

You're welcome, thanks for the feedback..

That's would work - as long as there are no carets in the original text.

Actually, it only matters if the ^ appears 'before' the first space in the original string (the manufacturer's name)
And it doesn't have to be a ^, it can be ANY character you like (or string of characters like "#%$&^")
What are the odds of that appearing in a manufacturer's name?
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","#%$&^",1)," ","-"),"#%$&^"," ",1)

Why a macro?

I have no place in my spreadsheet to actually put the formula

Seriously? You've used ALL the columns on your sheet (256 in xl2003, 16K+ in xl2007)
If your answer is YES, then well....Wow.

 

[Dr. Logic]

It's a great formula, thanks - instead of putting it in the spreadsheet I used it in a macro so that I would not be adding any column data to the spreadsheet.

 

[Jonmo1]

Great, glad to help.

 

[Rick Rothstein]

Try a formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","^",1)," ","-"),"^"," ",1)

The ^ is just a random character of your choice that is least likely to actually appear in the original string.

Just for the interest of it, here is another formula that would work (shorter, but still three function calls, not sure of the relative speeds against each other though)...

=REPLACE(SUBSTITUTE(A1," ","-"),FIND(" ",A1),1," ")

 

[Jonmo1]

Hmmm, I'll raise you 2 function calls....lol

=SUBSTITUTE(SUBSTITUTE(A1," ","-"),"-"," ",1)

 

[Rick Rothstein]

Hmmm, I'll raise you 2 function calls....lol

=SUBSTITUTE(SUBSTITUTE(A1," ","-"),"-"," ",1)

Ha, ha... I just came up with that for my macro (below) and was going to post back here with the formula version, but you beat me to that.

Sub ChangeAllSpacesExceptFirstToDashes()
  Dim R As Long, C As Long, vArr As Variant
  vArr = ActiveSheet.UsedRange
  For R = 1 To UBound(vArr)
    For C = 1 To UBound(vArr, 2)
      vArr(R, C) = Replace(Replace(vArr(R, C), " ", "-"), "-", " ", , 1)
    Next
  Next
  ActiveSheet.UsedRange = vArr
End Sub

This macro should work assuming the resources on the computer running permit it.

 

[Jonmo1]

Ha, ha... I just came up with that for my macro (below) and was going to post back here with the formula version, but you beat me to that.

Wish I had seen it that way in the first place