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

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.

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

Hi there! It's definitely possible to auto transform sets of data in Excel. Here are some steps you can follow for the examples you provided:

1. Changing "male" to "M" and "female" to "F":
   - Select the column containing the data you want to transform
   - Click on the "Replace" button in the "Editing" section of the Home tab
   - In the "Find what" field, type
   Code:

   ---

   "male"

   ---

   and in the "Replace with" field, type
   Code:

   ---

   "M"

   ---

   - Click "Replace All"
   - Repeat the process for
   Code:

   ---

   "female"

   ---

   to
   Code:

   ---

   "F"

   ---

2. Combining separate columns into one:
   - Insert a new column where you want the combined data to appear
   - In the first row of the new column, type the formula
   Code:

   ---

   "=A1&", "&B1&", "&C1&", "&D1&", "&E1"

   ---

   (assuming your data is in columns A through E)
   - Drag the formula down to apply it to all rows
   
3. Automatically populating a column based on the heading of the column next to it:
   - Insert a new column where you want the populated data to appear
   - In the first row of the new column, type the formula
   Code:

   ---

   "=IF(B1="Paid","Yes","No")"

   ---

   (assuming the column next to it contains the heading "Paid")
   - Drag the formula down to apply it to all rows

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

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

On Oct 25, 10:41 pm, Liz wrote:
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.