Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel
How to replace a column of values with another?
This is the column:-
N
N
N
N
N
N
N  
Which is North, But as I'm doing some tests, I want to change this to south : -
S
S
S
S
S
S
S
</pre>
The REPLACE function does not apply on all the column...
(There are over 1000 rows in that column)
Posted 25-Sep-12 0:25am
Edited 25-Sep-12 0:54am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

FIRST ANSWER
I'm sure there will be a formula for that, but as I'm not aware of that formula or macro, you could do it in the easy way:
 
Copy all the column to another excel.
Simply replace all the N to S.
Copy the column back to the original excel.
 
SECOND ANSWER
If they are consecutive, you can always:
  1. click the first cell of the column.
  2. hold ctrl + shift
  3. press the down cursor/arrow in your keyboard
This would select all the column at once.
 
Then apply my first answer...
 
Job done.
 
Good luck!
  Permalink  
v2
Comments
Sumal.V at 25-Sep-12 6:53am
   
Hmmm I must be replacing over 1000 values then :O
I must have mentioned that in the question..
Joan Murt at 25-Sep-12 6:59am
   
See updated answer...
Sumal.V at 25-Sep-12 7:27am
   
Did you mean manually replacing all the values ?
Joan Murt at 25-Sep-12 7:33am
   
No, if you move the entire column to another sheet, the you can press ctrl+r (to replace) and then you need only to replace all N to S.
I've recommended you to move the column to avoid replacing other N letters present in your sheet.
Sumal.V at 25-Sep-12 8:38am
   
Its ctrl + f in my computer which has find and replace option. But yes problem solved..:)
Joan Murt at 25-Sep-12 11:27am
   
I hate when Microsoft translate hotkeys...
 
Well, I'm glad it has been solved... Sometimes is better to use a manual method like this than using a macro/program to solve small problems... the time it takes to make the program is possibly bigger than making it by hand...
Kenneth Haugland at 25-Sep-12 7:10am
   
I have a suggestion too:
Write a program that does the job for you :-D
Sumal.V at 25-Sep-12 7:46am
   
I know how to do that in a macro, but its tedious. Moreover the excel file has many sheets and I'm only using this for testing.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 25 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100