MrExcel's Learn Excel #536 - Indeterminate Substitution

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Aug 4, 2009.
Robbie calls in today to ask how to add a hyphen at an unknown position in each cell. For example, whenever Excel finds a period, add a dash. There are three solutions to this problem. Episode 536 compares them all.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Bill Jelen: Welcome back to the MrExcel netcast I'm Bill Jelen.
Today our question sent in by Robbie, you send in a voicemail so let's have a look.
Robbie: Hey Bill my name is Robbie, I was wondering if there was a way to maybe add a hyphen in the middle of that hole after a period, let's say for example, let's say there's there may be a period in the cell either sometimes a third character space sometimes seventh character without using text to columns.
is there a similar way of being able to dive a hyphen into that?
Bill Jelen: hey, great question from Robbie, thanks to Robbie for calling in and it's always good to hear from our viewers.
There are three answers to this amazingly and the first answer is probably not going to be what Robbie was looking for, but I think it's the most straightforward, every time that we see a period we want to change it to a period hyphen, I'm going to go to edit, and choose replace, and then say every time we see a period change it to period hyphen and replace all, and very quickly now we've solved that problem.
But I think that Robbie was looking for a formula answer.
So let's go on to an identical copy of this sheet, and the first function I'm going to use is a cool little function called the substitute function.
The substitute function; we say hey we want to take that value that's over an A1 and every time that we find in quotes a period we want to substitute it with a period and a hyphen, and CONTROL ENTER to enter that in the whole value and you'll see that the substitute function works very, very well.
There's another function called replace which is much harder used because you have to say replace the seventh through the 10th character with certain text.
The substitute is clearly much easier to use, and then finally the third way to do this is kind of a two-step method where in cell B1 we ask the find command to find the period within cell A1 in it.
That tells us that it's a position three, and then I build this horrible formula that says hey we're going to take the leftmost three characters of cell A1 concatenate it.
The concatenation sign is the ampersand of course within quotes the hyphen, and then another ampersand, and then say we're going to take the mid of A1 starting at the period plus ONE, so in this case that be CHARACTER POSITION FOUR, and then how many characters do we need while we need the length of A1 minus the location of the period.
Put all that together and it will do the exact same thing.
So depending on exactly what you're trying to do, either edit, replace, or I think substitute is probably the best of these three or you can use the fine along with left mid and Len in order to solve the problem.
So there you have it, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top