Standardizing Capitalisation Formula

[Dopey_]

Good Day everyone,

I'm trying to standardize the capitalisation of products descriptions in an spreadsheet. Seems fairly straight forward but unfortunately the boss wants measurements to stay in lower case but wants to have the descriptions of the products in upper case. What I need to do is create a formula that finds the number and then put the measurement in lower case after it. So at the minute I have:

Steel hammer 25mm
Dormer drill 4mm
230 x 280mm Hand sanding sheets perfect

Using the formula: =SUBSTITUTE(UPPER(A1),"MM","mm"), the descriptions then convert to this:

STEEL HAmmER 25mm
[TABLE="width: 494"]
<tbody>[TR]
[TD="class: xl64, width: 494"]DORMER DRILL 4mm[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 494"]
<tbody>[TR]
[TD="class: xl65, width: 494"]230 x 280mm HAND SANDING SHEETS PERFECT

[/TD]
[/TR]
</tbody>[/TABLE]
Obviously, this doesn't work for me, is there better way of finding the measurements beforehand and then inserting them back in?

Cheers Guys, hope you can help save me a lot of time

 

[JackDanIce]

Try:

=SUBSTITUTE(UPPER(A1),"MM ", "mm ")

Change to yours: I've include a space after MM and mm

 

[Dopey_]

Hi Jack,

Unfortunately, because a lot of the measurements are at the end of the description, there is no space at the end of the "MM", so a lot of the descriptions will get missed out

Thanks anyway

 

[JackDanIce]

In that case, try:

=TRIM(SUBSTITUTE(UPPER(A1&" "), "MM ", "mm "))

 

[West Man]

Hi Jack,

Unfortunately, because a lot of the measurements are at the end of the description, there is no space at the end of the "MM", so a lot of the descriptions will get missed out

Thanks anyway

Then add a space at the end. =TRIM(SUBSTITUTE(UPPER(A1)&" ","MM ", "mm "))

 

[pepe74287]

Much more complicated formula could be:

=REPLACE(UPPER(A1),SEARCH("mm",A1,MAX(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))*(ROW(INDIRECT("1:"&LEN(A1)))))),2,"mm")

confirmed with ctrl-shift-enter.

But probably I would also go with =TRIM(SUBSTITUTE(UPPER(A1&" "), "MM ", "mm ")) unless the initial text would be something like "230mm mumm" in which case you would get "230mm MUmm".

 

[JackDanIce]

Agree, I suspect there are only a very small number of words that make up product descriptions that end in mm and for such a small count of exception cases, better to go with a formula that covers the majority of cases.

 

[Dopey_]

Thanks guys, West Man your formula worked great. It's always the case with these things that after a while you just need someone to come along and say it's a lot easier than what you're making out to be.