Find and replace

A

alistairp

I receive data once a day into an Excel spreadsheet and need to convert 26 2
digit different labels to words.

For example RD needs to be converted to READING.

I am using find and replace but the data does not save for the next day.

Is there any way that I can create 26 templates that I can then use without
having to type the full words every day.
 
M

Max

One way is to use a sub posted by Gary''s Student [Sub transla() below] which
can do all of your 26 multiple find n replace at one go

Here's the easy steps to implement:
(Try on a spare copy of your file)

Install GS' sub into a regular module as below:
In your excel file:
1. Press Alt+F11 to go to VBE
In VBE, click Insert > Module
Copy n paste GS' sub (below) into the code window
(everything within the dotted lines)

'----------
Sub transla()
'GS .newusers
Dim inn() As String, outt() As String
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub
'---------

Press Alt+Q to get back to Excel

2. In Excel,
a. Insert a new sheet, name it as: xlator
List the text that you want to FIND in col A (ie list your 26 2 digit labels)
List the corresponding full text to REPLACE it with in col B

b. Go to the sheet that you have the text to be found n replaced all at one
go (this text could be all over the place within the sheet)

Rename the sheet as: Sheet2

Alternatively, you could go back to VBE
and amend this line in the sub to reflect your actual sheetname:

Sheets("Sheet2").Activate

c. Press Alt+F8 to bring up the Macro dialog
Either double-click directly on "transla" inside the window,
or select "transla", click Run, to run the sub.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 
A

alistairp

Thanks Max

This works until the final step when all data within the worksheet is being
amended.

I need to Macro to say something similar to the tick box in Find & Replace
that says 'Match entire cell contents' only.

For example I am amending OS to read OXFORD STREET. OS is in Column A and
despite highlighting this column when I click Run all text in the spreadsheet
is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then it
is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC.

Presumably I have missed something.

Any further assistance would be appreciated.

Alistair


Max said:
One way is to use a sub posted by Gary''s Student [Sub transla() below] which
can do all of your 26 multiple find n replace at one go

Here's the easy steps to implement:
(Try on a spare copy of your file)

Install GS' sub into a regular module as below:
In your excel file:
1. Press Alt+F11 to go to VBE
In VBE, click Insert > Module
Copy n paste GS' sub (below) into the code window
(everything within the dotted lines)

'----------
Sub transla()
'GS .newusers
Dim inn() As String, outt() As String
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub
'---------

Press Alt+Q to get back to Excel

2. In Excel,
a. Insert a new sheet, name it as: xlator
List the text that you want to FIND in col A (ie list your 26 2 digit labels)
List the corresponding full text to REPLACE it with in col B

b. Go to the sheet that you have the text to be found n replaced all at one
go (this text could be all over the place within the sheet)

Rename the sheet as: Sheet2

Alternatively, you could go back to VBE
and amend this line in the sub to reflect your actual sheetname:

Sheets("Sheet2").Activate

c. Press Alt+F8 to bring up the Macro dialog
Either double-click directly on "transla" inside the window,
or select "transla", click Run, to run the sub.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
alistairp said:
I receive data once a day into an Excel spreadsheet and need to convert 26 2
digit different labels to words.

For example RD needs to be converted to READING.

I am using find and replace but the data does not save for the next day.

Is there any way that I can create 26 templates that I can then use without
having to type the full words every day.
Click to expand...
Click to expand...
 
M

Max

Perhaps a simpler process using vlookup would suffice?

Reference list is assumed in sheet: xlator, cols A and B
where col A = items to find, col B = what to replace it with

With source data assumed in A2 down in the other sheet
Put in B2:
=IF(A2="","",IF(ISNA(VLOOKUP(A2,xlator!A:B,2,0)),A2,VLOOKUP(A2,xlator!A:B,2,0)))
Copy B2 down to the last row of data in col A. Then copy col B, overwrite
col A with a paste special as values. Delete col B.


If you want to pursue the sub approach, try a post in .programming. Or hang
around here awhile, maybe other responders versed in vba might chime in.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 
R

Roger Govier

Hi

The minor modification of the code worked fine for me
Sub transla()
'GS .newusers
Dim inn() As String, outt() As String
Dim n As Long, i As Long, r As Range, v As Variant
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub


--
Regards
Roger Govier

alistairp said:
Thanks Max

This works until the final step when all data within the worksheet is
being
amended.

I need to Macro to say something similar to the tick box in Find & Replace
that says 'Match entire cell contents' only.

For example I am amending OS to read OXFORD STREET. OS is in Column A and
despite highlighting this column when I click Run all text in the
spreadsheet
is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then
it
is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC.

Presumably I have missed something.

Any further assistance would be appreciated.

Alistair


Max said:
One way is to use a sub posted by Gary''s Student [Sub transla() below]
which
can do all of your 26 multiple find n replace at one go

Here's the easy steps to implement:
(Try on a spare copy of your file)

Install GS' sub into a regular module as below:
In your excel file:
1. Press Alt+F11 to go to VBE
In VBE, click Insert > Module
Copy n paste GS' sub (below) into the code window
(everything within the dotted lines)

'----------
Sub transla()
'GS .newusers
Dim inn() As String, outt() As String
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub
'---------

Press Alt+Q to get back to Excel

2. In Excel,
a. Insert a new sheet, name it as: xlator
List the text that you want to FIND in col A (ie list your 26 2 digit
labels)
List the corresponding full text to REPLACE it with in col B

b. Go to the sheet that you have the text to be found n replaced all at
one
go (this text could be all over the place within the sheet)

Rename the sheet as: Sheet2

Alternatively, you could go back to VBE
and amend this line in the sub to reflect your actual sheetname:

Sheets("Sheet2").Activate

c. Press Alt+F8 to bring up the Macro dialog
Either double-click directly on "transla" inside the window,
or select "transla", click Run, to run the sub.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
alistairp said:
I receive data once a day into an Excel spreadsheet and need to convert
26 2
digit different labels to words.

For example RD needs to be converted to READING.

I am using find and replace but the data does not save for the next
day.

Is there any way that I can create 26 templates that I can then use
without
having to type the full words every day.
Click to expand...
Click to expand...
Click to expand...
 
A

alistairp

vlookup does the trick. Many thanks for your help.

Alistair

Roger Govier said:
Hi

The minor modification of the code worked fine for me
Sub transla()
'GS .newusers
Dim inn() As String, outt() As String
Dim n As Long, i As Long, r As Range, v As Variant
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub


--
Regards
Roger Govier

alistairp said:
Thanks Max

This works until the final step when all data within the worksheet is
being
amended.

I need to Macro to say something similar to the tick box in Find & Replace
that says 'Match entire cell contents' only.

For example I am amending OS to read OXFORD STREET. OS is in Column A and
despite highlighting this column when I click Run all text in the
spreadsheet
is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then
it
is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC.

Presumably I have missed something.

Any further assistance would be appreciated.

Alistair


Max said:
One way is to use a sub posted by Gary''s Student [Sub transla() below]
which
can do all of your 26 multiple find n replace at one go

Here's the easy steps to implement:
(Try on a spare copy of your file)

Install GS' sub into a regular module as below:
In your excel file:
1. Press Alt+F11 to go to VBE
In VBE, click Insert > Module
Copy n paste GS' sub (below) into the code window
(everything within the dotted lines)

'----------
Sub transla()
'GS .newusers
Dim inn() As String, outt() As String
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub
'---------

Press Alt+Q to get back to Excel

2. In Excel,
a. Insert a new sheet, name it as: xlator
List the text that you want to FIND in col A (ie list your 26 2 digit
labels)
List the corresponding full text to REPLACE it with in col B

b. Go to the sheet that you have the text to be found n replaced all at
one
go (this text could be all over the place within the sheet)

Rename the sheet as: Sheet2

Alternatively, you could go back to VBE
and amend this line in the sub to reflect your actual sheetname:

Sheets("Sheet2").Activate

c. Press Alt+F8 to bring up the Macro dialog
Either double-click directly on "transla" inside the window,
or select "transla", click Run, to run the sub.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
:
I receive data once a day into an Excel spreadsheet and need to convert
26 2
digit different labels to words.

For example RD needs to be converted to READING.

I am using find and replace but the data does not save for the next
day.

Is there any way that I can create 26 templates that I can then use
without
having to type the full words every day.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find/Replace specific indent 1
Custom toolbar button for search and replace 1
Removing a repeated words 0
Find and replace macro for Special ^p - MS Word 0
Word macro that can call cells out of Excel for a find and replace 0
Remove numbers from brackets 0
Word VBA - Want to test if Full Name is NOT Present in document 0
Word- Find and Replace 1

Top