Find and Replace

[Historyguy40]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi Everyone,

I have a spreadsheet that has almost 50,000 lines. In one column, I have a numeric value in each cell (a zip code, 90210, for example). I need to add the words "Zip Code" to each cell without losing the numeric value. I do not wish to type "Zip Code" 50,000 times. I did this once before using Find & Replace, but cannot duplicate it again. I know its a simple process, but I can't figure it out. Any suggestions would be great!

 

[pjonesCET]

Would the following work:

Edit Menu > Find and replace:

type in say first three numbers of zip codes in Find.
Next type Zip code plus the the three numbers if they have to be within the cells.

I would actually name the column the zip codes are in "Zip Code" (without the "'s)

 

[Historyguy40]

Thank you!

I tried that, but it did not work. I remember having to type something to keep the original value, but I cannot remember what it was. I thought it might be using "$" or "*", but none of them worked. When I did it years ago, it was very simple.

The reason for wanting to add the words "Zip Code", is that my spreadsheet feeds a FileMaker Pro program. When I transfer the data, only the number shows up. Without the words, the values are meaningless.

 

[John_McGhie_[MVP]]

You need a "Concatenation formula".

Create a new column beside your Zipcode column (you can remove it later, but
you need it for now).

In that column, in the top cell, type ="Zip Code " &

Then click in the top cell in the zip code column and press Return

You will see a formula such as ="Zip Code " & A1 Note the position of the
quote marks: it won't work without them.

Now, click in that cell, and copy.

Select the whole column (Control + Spacebar)

And Paste. That will fill the top 1,048,576 cells for you...

There you are, job's done

Hope this helps

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
Hi Everyone,

I have a spreadsheet that has almost 50,000 lines. In one column, I have a
numeric value in each cell (a zip code, 90210, for example). I need to add
the words "Zip Code" to each cell without losing the numeric value. I do not
wish to type "Zip Code" 50,000 times. I did this once before using Find &
Replace, but cannot duplicate it again. I know its a simple process, but I
can't figure it out. Any suggestions would be great!

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Mungo]

Hi Everyone,

>
> I have a spreadsheet that has almost 50,000 lines. In one column, I have a numeric value in each cell (a zip code, 90210, for example). I need to add the words "Zip Code" to each cell without losing the numeric value. I do not wish to type "Zip Code" 50,000 times. I did this once before using Find & Replace, but cannot duplicate it again. I know its a simple process, but I can't figure it out. Any suggestions would be great!

What I would do is (after u take a copy of the data/file just in case) is change the zip code numerics to text, insert a column either before or after depending where u wanted "zip code" to appear, type in "zip code" in the first cell and copy it down 50000 times, then go to a blank cell and use @concatenate function to add the cell contents, drag this formula down for all rows, then finally reorganise the spreadsheet.
Note, concentenate is a text function - look in help before u go if u have not used this before, also be mindful of copy and paste special.

 

[John_McGhie_[MVP]]

Phillip:

Did you test it?

Cheers

Would the following work:

Edit Menu > Find and replace:

type in say first three numbers of zip codes in Find.
Next type Zip code plus the the three numbers if they have to be within the
cells.

I would actually name the column the zip codes are in "Zip Code" (without the
"'s)

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Carl Witthoft]

I dont' think you are using FileMaker correctly either. In general you
should always keep numeric values numeric. Tell FileMaker the NAME of
the variable you are importing. Don't dump text into a numeric field.

 

[CyberTaz]

FWIW, I'm kinda on the same page as Carl here... There must be some
significant details you haven't disclosed

When data is exported from or imported to most any source there is
absolutely no reason why every field of every record need be individually
labeled - in fact, it most commonly shouldn't be & it fouls things up if it
is. I.E., Being stored in a field called "Zip Code" there is reason for each
individual zip code to bear that label. "Only the number" should show up,
but it should show up in the right place - the corresponding zip code field
for each record, just like every other data item (first name, last name,
street address, city, etc.) should map to the appropriate field in the
receiving app. The data shouldn't be "meaningless" if it's stored properly.

Perhaps if you'd provide a detailed description of how you have what data
stored in Excel, the specific means by which you "transfer" the data, as
well as what exact result you need to achieve someone will be able to offer
a more useful suggestion.

On an additional note, the reason I wrote "kinda on the same page" above is
that experience has taught me that whenever possible data fields should not
be defined as values (number data types) just because the field content is
strictly numerical digits. If the data will not be used for calculations
(when's the lat time you summed a list of zip codes or found the sq rt of
one ? ) it's best defined as a Text field. Further, if any method is used
to combine "zip code" & "90210" in an Excel cell the resulting content will
not be recognized as a numerical value by Excel, it will be exported as a
text string & will be read as such by what ever program is used to import
it... There is no such number as 'zipcode90210'.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt [MVP]]

The easy way is to change the format to append the text to the value,
or vice versa.

The custom number format would then be; "Zip code "00000

 

[John_McGhie_[MVP]]

{Slap!} I shoulda thought of that

The "consideration" of course, is that if he then wants to save the data
anywhere else, the text label may not go with it.

Thanks Bob

The easy way is to change the format to append the text to the value,
or vice versa.

The custom number format would then be; "Zip code "00000

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Historyguy40]

You need a "Concatenation formula".

>
> Create a new column beside your Zipcode column (you can remove it later, but
> you need it for now).
>
> In that column, in the top cell, type ="Zip Code " &
>
> Then click in the top cell in the zip code column and press Return
>
> You will see a formula such as ="Zip Code " & A1 Note the position of the
> quote marks: it won't work without them.
>
> Now, click in that cell, and copy.
>
> Select the whole column (Control + Spacebar)
>
> And Paste. That will fill the top 1,048,576 cells for you...
>
> There you are, job's done
>
> Hope this helps
>
> On 5/06/10 10:01 AM, in article (e-mail address removed)2ac0,
> "[email protected]" wrote:
>
>
> --
>
> The email below is my business email -- Please do not email me about forum
> matters unless I ask you to; or unless you intend to pay!
>
> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
> McGhie Information Engineering Pty Ltd
> Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]
>
> Thank you very much! Your suggestion worked perfectly!
>