Need help replacing values in multiple cells

**philwalker87** · 03-08-2017, 05:03 PM

OK, so I need a hand with the following, if anyone can help it would be appreciated!

I have around 30,000 lines of data; an example of a few lines shown below

Limited Edition 1916 - Womens T-Shirt - Small - Azalea - 100th Birthday
Limited Edition 1916 - Womens T-Shirt - Medium - Azalea - 100th Birthday
Limited Edition 1916 - Womens T-Shirt - Large - Azalea - 100th Birthday
Limited Edition 1917 - Womens T-Shirt - Small - Azalea - 99th Birthday
Limited Edition 1917 - Womens T-Shirt - Medium - Azalea - 99th Birthday
Limited Edition 1917 - Womens T-Shirt - Large - Azalea - 99th Birthday
Limited Edition 1918 - Womens T-Shirt - Small - Azalea - 98th Birthday
Limited Edition 1918 - Womens T-Shirt - Medium - Azalea - 98th Birthday
Limited Edition 1918 - Womens T-Shirt - Large - Azalea - 98th Birthday

etc etc etc

all the way down to Limited Edition 2000 - Womens T-Shirt - Large - Azalea - 16th Birthday

What I need to do is replace all the 100th Birthday's with 101st Birthday and all the 99th Birthday's with 100 Birthday, 98th's with 99th all the way down to zero without having to do a Find / Replace 100 times! Not sure if I'm just being dumb or if it requires some kind of VBA to do. I would normally use text to columns for this kind of thing and replace the - 100th Birthday with | 100th Birthday to put it into a unique cell but the data isn't consistent all the way down so will need to do a Find and replace someway (I think!)

Any help would be appreciated..!

**Amanda Wilso** · 03-08-2017, 05:11 PM

Open the Excel Find and Replace dialog by pressing the Ctrl + F shortcut. Alternatively, go to the Home tab > Editing group and click Find & Select > Find.
Then in the Find what box, type the characters (text or number) you are looking for and click either Find All And Replace all 100th with 101st, 99th with 100th etc.

**xladept** · 03-08-2017, 06:03 PM

Well, it works on your sample

Sub PhilW(): Dim UR As Range, n As Long, m As Long
Dim C As Range, S As String, Q As String
Set UR = ActiveSheet.UsedRange
For Each C In UR: S = C.Value
n = InStr(1, S, "Birthday")
If n = 0 Then GoTo NextCell
m = InStrRev(S, " ", n - 3) + 1
Q = Mid(S, m, n - m - 3)
If IsNumeric(Q) Then
Mid(S, m, n - m - 3 + 1) = CStr(Val(Q) + 1)
C.Value = S: End If
NextCell: Next
UR.Replace "0h", "0th"
UR.Replace "1th", "1st"
UR.Replace "2th", "2nd"
UR.Replace "3th", "3rd"
UR.Replace "2st", "2nd"
UR.Replace "3nd", "3rd"
UR.Replace "4rd", "4th"
End Sub

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

**philwalker87** · 03-08-2017, 06:21 PM

Thanks for the reply, here's the output:

Limited Edition 1916 - Womens T-Shirt - Small - Azalea - 101th Birthday
Limited Edition 1916 - Womens T-Shirt - Medium - Azalea - 101th Birthday
Limited Edition 1916 - Womens T-Shirt - Large - Azalea - 101th Birthday
Limited Edition 1917 - Womens T-Shirt - Small - Azalea - 10th Birthday
Limited Edition 1917 - Womens T-Shirt - Medium - Azalea - 10th Birthday
Limited Edition 1917 - Womens T-Shirt - Large - Azalea - 10th Birthday
Limited Edition 1918 - Womens T-Shirt - Small - Azalea - 99th Birthday
Limited Edition 1918 - Womens T-Shirt - Medium - Azalea - 99th Birthday
Limited Edition 1918 - Womens T-Shirt - Large - Azalea - 99th Birthday

A couple of issues, I would want the 101th to be displayed as 101st - the same for numbers like 93RD and 92ND etc..

Also the 1917 displays as 10th birthday and not 100th birthday?!

Arrgh!

**xladept** · 03-08-2017, 06:28 PM

Hi Phil,

Try it now - I've done quite a bit of editing

Data Range

	C
4	Limited Edition 1916 - Womens T-Shirt - Small - Azalea - 101st Birthday
5	Limited Edition 1916 - Womens T-Shirt - Medium - Azalea - 101st Birthday
6	Limited Edition 1916 - Womens T-Shirt - Large - Azalea - 101st Birthday
7	Limited Edition 1917 - Womens T-Shirt - Small - Azalea - 100th Birthday
8	Limited Edition 1917 - Womens T-Shirt - Medium - Azalea - 100th Birthday
9	Limited Edition 1917 - Womens T-Shirt - Large - Azalea - 100th Birthday
10	Limited Edition 1918 - Womens T-Shirt - Small - Azalea - 99th Birthday
11	Limited Edition 1918 - Womens T-Shirt - Medium - Azalea - 99th Birthday
12	Limited Edition 1918 - Womens T-Shirt - Large - Azalea - 99th Birthday

**philwalker87** · 03-09-2017, 07:48 AM

Do you have the code?

**xladept** · 03-09-2017, 09:30 AM

It's in post #3.

**philwalker87** · 03-09-2017, 12:18 PM

Cool works.....!

Just realised though that some of the data has 101st and some have 101st Birthday, so will currently only update the "101st Birthday" and not 101st - how can I manipulate to accommodate for both variables?

Thanks for all your help so far!

Regards

Phil

**xladept** · 03-09-2017, 05:58 PM

Hi Phil,

Can you post a sample of the aberration!

**philwalker87** · 03-10-2017, 08:07 AM

Hi

Like this:

Made In 1915 British Parts Crown - 101st Birthday - Mens T-Shirt - 13 Colours
Made In 1915 British Parts Crown - 101st - Mens T-Shirt - Red - XS
Made In 1915 British Parts Crown - 101st - Mens T-Shirt - Red - Small
Made In 1915 British Parts Crown - 101st - Mens T-Shirt - Red - Medium
Made In 1915 British Parts Crown - 101st - Mens T-Shirt - Red - Large
Made In 1915 British Parts Crown - 101st - Mens T-Shirt - Red - XL
Made In 1915 British Parts Crown - 101st - Mens T-Shirt - Red - XXL
Made In 1915 British Parts Crown - 101st - Mens T-Shirt - Red - XXXL

Thanks!

**xladept** · 03-10-2017, 07:48 PM

Hi Phil,

Here's my next attempt:

Sub PhilWXX(): Dim UR As Range, n As Long, m As Long
Dim C As Range, S As String, Q As String
Set UR = ActiveSheet.UsedRange
For Each C In UR: S = C.Value
n = InStr(1, S, "Birthday")
If n = 0 Then
m = InStr(1, S, " - ")
Do Until Not IsNumeric(Mid(S, m + 3, n + 1)): n = n + 1: Loop
Q = Mid(S, m + 3, n)
        If IsNumeric(Q) Then
    Mid(S, m + n, n) = CStr(Val(Q) + 1): C.Value = S
        End If
Else
m = InStrRev(S, " ", n - 3) + 1
Q = Mid(S, m, n - m - 3)
        If IsNumeric(Q) Then
Mid(S, m, n - m - 3 + 1) = CStr(Val(Q) + 1): C.Value = S
        End If
End If

NextCell: Next
UR.Replace "0h", "0th"
UR.Replace "1th", "1st"
UR.Replace "2th", "2nd"
UR.Replace "3th", "3rd"
UR.Replace "2st", "2nd"
UR.Replace "3nd", "3rd"
UR.Replace "4rd", "4th"
UR.Replace "11st", "11th"
UR.Replace "12nd", "12th"
UR.Replace "13rd", "13th"
End Sub

Need help replacing values in multiple cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Re: Need help replacing values in multiple cells

Thread Information

Users Browsing this Thread

Similar Threads

Finding and Replacing multiple values at a time

finding and replacing multiple values

Replacing the NA values with blank cells

Find and replacing multiple values VBA

Help needed replacing multiple cells from a list of values.

Help needed replacing multiple cells from a list of values.

Replacing Values in Cells Via Formula

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions