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