# Advanced find and replace specific text within a cell

1. ## Advanced find and replace specific text within a cell

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?

2. ## Re: Advanced find and replace specific text within a cell

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.

3. ## Re: Advanced find and replace specific text within a cell

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

4. ## Re: Advanced find and replace specific text within a cell

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

5. ## Re: Advanced find and replace specific text within a cell

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.

6. ## Re: Advanced find and replace specific text within a cell

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.

7. ## Re: Advanced find and replace specific text within a cell

Perhaps this
``Please Login or Register  to view this content.``

8. ## Re: Advanced find and replace specific text within a cell

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1