Advanced find and replace specific text within a cell

**visionwindowfilms** · 03-26-2011, 01:13 PM

In column F Ive got a list of skus which look similar to this - DEC-RAINB-0916-1

At the end of every one is a number from 1 to 50. I want to make it so that the single numbers (ie 1-9 still have two numbers so 01, 02, 03). Obviously if I do a simple find and replace it will mess up the two figure numbers too (ie find 1 and replace with 01 will also change the number 10 to 010). Is there an easy way around this?

**MarvinP** · 03-26-2011, 01:29 PM

Hi,
Put your test in A1 and try this formula on it

Please Login or Register  to view this content.

This will use the last digit of your sku and return a sku with a 2 digit (really text) ending.
Note you will have to adjust to work with col F and a zero on the last digit will need to be included in the choose function.

Also - I hope you don't have a 2 digit on the end of you sku's

hope this works for you.

**Marcol** · 03-26-2011, 01:42 PM

Here's a bit of a mouthful
In a spare column row #2, with your data beginning F2

Please Login or Register  to view this content.

This should work for any amount of digits after the last hyphen

@ Marvin
I can't get your formula to run past -9

**MarvinP** · 03-26-2011, 02:19 PM

I thought he had only a single digit (1 to 9) as the question. If he had stuff after the dash like "-43" I'd return a "-403".

The Choose shouldn't work after 9 as there are only 9 choices. After posting it I realized I didn't cover the digit of zero. I should have really done a:

Please Login or Register  to view this content.

**MarvinP** · 03-26-2011, 02:55 PM

OK how about this formula that should put a zero in front of those single digit last numbers and leave the 2 digit ones alone.

Please Login or Register  to view this content.

I hope this is better than my last choose function.

**visionwindowfilms** · 04-14-2011, 02:59 AM

Originally Posted by MarvinP

OK how about this formula that should put a zero in front of those single digit last numbers and leave the 2 digit ones alone.

Please Login or Register  to view this content.

I hope this is better than my last choose function.

Ive been on holiday so havent tried these yet but I think this last one sounds the most promising as there are two digits also ie 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 etc up to 50 so I only want to add the zero before the single digits 1-9 - Ill give them a go when I get back to the office later today and let you know how I get on.

**johnjohns** · 04-14-2011, 03:35 AM

Perhaps this

Please Login or Register  to view this content.

**Marcol** · 04-14-2011, 04:28 AM

This might be simpler

Please Login or Register  to view this content.

[EDIT]
If the suffix could be "-0" then

Please Login or Register  to view this content.

Hope this helps

Advanced find and replace specific text within a cell

LinkBack

Thread Tools

Rate This Thread

Display

Advanced find and replace specific text within a cell

Re: Advanced find and replace specific text within a cell

Re: Advanced find and replace specific text within a cell

Re: Advanced find and replace specific text within a cell

Re: Advanced find and replace specific text within a cell

Re: Advanced find and replace specific text within a cell

Re: Advanced find and replace specific text within a cell

Re: Advanced find and replace specific text within a cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions