I've got office 2007 suite. I have some data on excel and it is in
currently, text format. I want to be able to convert the text into
number. An example (not that you don't already know what I am talking
about), I want to be able to convert the word "Positive" to 4 or
"Disagree" to 5.

If anyone has any reference of doing that, I would appreciate it.

Set up a table for the text and the numerical equivalents and then use
VLookup.

--

Regards,
Nigel




wrote in message
...
I've got office 2007 suite. I have some data on excel and it is in
currently, text format. I want to be able to convert the text into
number. An example (not that you don't already know what I am talking
about), I want to be able to convert the word "Positive" to 4 or
"Disagree" to 5.

If anyone has any reference of doing that, I would appreciate it.

Are you talking about physically replacing the word "Positive" with the
number 4 within the same cell (same for your other substitutions)? If so,
you will need a macro to do that. You would want to use the Worksheet Change
event and use the Replace function to perform the actual substitutions.
There are a few ways to handle the replacement lists... kind of depends on
how many replacements we are talking about... 5 or 500. If this is the way
you need to go, post back with more details about what you need to do.

Rick


wrote in message
...
I've got office 2007 suite. I have some data on excel and it is in
currently, text format. I want to be able to convert the text into
number. An example (not that you don't already know what I am talking
about), I want to be able to convert the word "Positive" to 4 or
"Disagree" to 5.

If anyone has any reference of doing that, I would appreciate it.

On 22 Dec, 09:39, "Rick Rothstein \(MVP - VB\)"
wrote:
Are you talking about physically replacing the word "Positive" with the
number 4 within the same cell (same for your other substitutions)? If so,
you will need a macro to do that. You would want to use the Worksheet Change
event and use the Replace function to perform the actual substitutions.
There are a few ways to handle the replacement lists... kind of depends on
how many replacements we are talking about... 5 or 500. If this is the way
you need to go, post back with more details about what you need to do.

Rick

wrote in message

...



I've got office 2007 suite. I have some data on excel and it is in
currently, text format. I want to be able to convert the text into
number. An example (not that you don't already know what I am talking
about), I want to be able to convert the word "Positive" to 4 or
"Disagree" to 5.

If anyone has any reference of doing that, I would appreciate it.- Hide quoted text -

- Show quoted text -

Hi,

Why not just use Excel's find and replace function?

You can specify the column you wish to search for "Positive" or
"Disagree" then Ctrl + F in the find criteria "Find what:" "Positive"
then "Replace with:" "4"

Are you talking about physically replacing the word "Positive" with the
number 4 within the same cell (same for your other substitutions)? If so,
you will need a macro to do that. You would want to use the Worksheet
Change
event and use the Replace function to perform the actual substitutions.
There are a few ways to handle the replacement lists... kind of depends
on
how many replacements we are talking about... 5 or 500. If this is the
way
you need to go, post back with more details about what you need to do.

Rick

wrote in message

...



I've got office 2007 suite. I have some data on excel and it is in
currently, text format. I want to be able to convert the text into
number. An example (not that you don't already know what I am talking
about), I want to be able to convert the word "Positive" to 4 or
"Disagree" to 5.

If anyone has any reference of doing that, I would appreciate it.- Hide
quoted text -

- Show quoted text -

Hi,

Why not just use Excel's find and replace function?

You can specify the column you wish to search for "Positive" or
"Disagree" then Ctrl + F in the find criteria "Find what:" "Positive"
then "Replace with:" "4"

Were you responding to me or the OP?

If you were responding to me, my thoughts were this... I was thinking he
might be doing this for more than one loading of data, that he could be
doing it for (possibly a lot) more than 2 substitutions and wouldn't want to
do the process manually each time.

Rick

On Dec 22, 7:42*am, wrote:
On 22 Dec, 09:39, "Rick Rothstein \(MVP - VB\)"





wrote:
Are you talking about physically replacing the word "Positive" with the
number 4 within the same cell (same for your other substitutions)? If so,
you will need a macro to do that. You would want to use the Worksheet Change
event and use the Replace function to perform the actual substitutions.
There are a few ways to handle the replacement lists... kind of depends on
how many replacements we are talking about... 5 or 500. If this is the way
you need to go, post back with more details about what you need to do.

Rick

wrote in message

...

I've got office 2007 suite. I have some data on excel and it is in
currently, text format. I want to be able to convert the text into
number. An example (not that you don't already know what I am talking
about), I want to be able to convert the word "Positive" to 4 or
"Disagree" to 5.

If anyone has any reference of doing that, I would appreciate it.- Hide quoted text -

- Show quoted text -

Hi,

Why not just use Excel's find and replace function?

You can specify the column you wish to search for "Positive" or
"Disagree" then Ctrl + F in the find criteria "Find what:" "Positive"
then "Replace with:" "4"- Hide quoted text -

- Show quoted text -

Yep...it works..I didn't know why I didn't think in simpler manner.
Thanks a bunch....