How do I auto transform sets of data? (change "female" to "f")

[Guest]

I need to figure out how to automatically transform data and/or populate
cells. Some examples of the things I need to do:
-change all instances of "male" to "M" and all instances of "female" to "F"
-combine separate columns with street, city, state, zip, & country into one
column
-automatically populate a column asking whether work experience was paid or
voluntary based on the heading of the column next to it

I don't know if all of these things are possible but any help you could
offer would be greatly appreciated.

Thanks.

 

[Pete_UK]

You could use Find & Replace to change Male and Female, or you could
use this formula:

=IF(A1="Female","F",IF(A1="Male","M","")
or even:
=LEFT(A1,1)

where I have assumed your gender information is in column A starting
with A1 - adjust the cell references to suit and copy the formula down
as required.

Assuming you have street, state, zip and country in four columns D to
G, then this formula will combine them with a comma and space between
each:

=D1&", "&E1&", "&F1&", "&G1

Again copy this down as required.

I'm not sure what you mean about the work experience - perhaps you can
elaborate?

Hope this helps.

Pete

 

[Doha]

What formula should I use if I want to get (female) if I type f and to get ( male) if I type M in excel sheet ?

it is urgent please answer me ,
thanks

 

[Doha]

while using excel , i want to type letter f and get word female and m to get word male ?
how could i apply it with a special formula ?