Who can write this little macro??

Dr. Logic

Board Regular
Joined
Jul 13, 2005
Messages
218
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Jul 13, 2005
Messages
218
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Jul 13, 2005
Messages
218

ADVERTISEMENT

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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Hmmm, I'll raise you 2 function calls....lol

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.

Code:
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.
 

Forum statistics

Threads
1,137,298
Messages
5,680,678
Members
419,924
Latest member
Dhamodharan992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top