Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 4th 08, 11:26 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 4
Default Find and replace

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.

  #2   Report Post  
Old December 4th 08, 12:57 PM posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Find and replace

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" wrote:
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.

  #3   Report Post  
Old December 4th 08, 04:01 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 4
Default Find and replace

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" wrote:

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" wrote:
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.

  #4   Report Post  
Old December 5th 08, 12:31 AM posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Find and replace

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)),A 2,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
---
"alistairp" wrote:
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


  #5   Report Post  
Old December 5th 08, 01:02 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default Find and replace

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" wrote in message
...
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" wrote:

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" wrote:
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.




  #6   Report Post  
Old December 5th 08, 10:18 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 4
Default Find and replace

vlookup does the trick. Many thanks for your help.

Alistair

"Roger Govier" wrote:

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" wrote in message
...
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" wrote:

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" wrote:
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.



  #7   Report Post  
Old December 5th 08, 10:19 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 4
Default Find and replace

vlookup does the trick. Many thanks for your help.

Alistair

"Max" wrote:

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)),A 2,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
---
"alistairp" wrote:
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


  #8   Report Post  
Old December 5th 08, 12:57 PM posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Find and replace

Good to hear it did.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"alistairp" wrote in message
...
vlookup does the trick. Many thanks for your help.

Alistair





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find & Replace: find part cell, replace whole cell katy Excel Worksheet Functions 2 June 19th 08 01:39 PM
Find and Replace - Replace with Blank Space Studebaker Excel Discussion (Misc queries) 3 October 11th 07 11:39 PM
where to put results of find operation in find and replace functio DEP Excel Worksheet Functions 5 November 15th 06 07:52 PM
find and replace - replace data in rows to separated by commas msdker Excel Worksheet Functions 1 April 15th 06 01:00 AM
find replace cursor default to find box luffa Excel Discussion (Misc queries) 0 February 3rd 05 12:11 AM


All times are GMT +1. The time now is 01:26 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017