• Welcome to AppraisersForum.com, the premier online  community for the discussion of real estate appraisal. Register a free account to be able to post and unlock additional forums and features.

Forum Sponsor - a la mode

QuickSource provides a single-source solution to easily import, compare, and manage data from multiple, credible sources in every report. See what the next game-changer is really all about.

Combining fields in excel

Status
Not open for further replies.

Tom Woolford

Thread Starter
Elite Member
Joined
Nov 20, 2005
Professional Status
Certified Residential Appraiser
State
Florida
I have several spreadsheets that have two separate fields for month and year. I would like to combine them into a single field (07/2008). I'm pretty good with excel, but this one has me stumped. Any ideas?
 

Terrel L. Shields

Elite Member
Gold Supporting Member
Joined
May 2, 2002
Professional Status
Certified General Appraiser
State
Arkansas
have two separate fields for month and year
since a date is recognized as a single number beginning Jan. 1, 1900 or some such, I doubt you can successfully merge the two fields.
 

Howard Klahr

Senior Member
Joined
Oct 4, 2004
Professional Status
Certified General Appraiser
State
Florida
In order to combine the two fields into one assuming the data is in column A (Month) and Column B (Year) enter the following formula in Column C:
+"'"&a1&"/"&b1

the formula starts off with a plus sign to signify a formula, the next character is a double quote, then a single quote and another double qoute. This lets excel know the result is to be text rather than numbers. The ampersand is the operator used to combine text a1 is the cell reference for the first field. The backslash surrounded by double quotes seperates the month from the year. b1 is the cell reference for the second field. Remember the result will be a text field not a date and therefore you can't perform calculations on the result.

Hope this helps.
 

deturner

Senior Member
Joined
Apr 2, 2003
Professional Status
Certified Residential Appraiser
State
Kansas
Howard, I've never seen the + symbol work and it can be formatted to do calculations on.

Here is the process:
Assuming your data is in column A and Column B, place this formula in Column C:
=A1&"/"&B1 (don't know where the extra quotes Howard used came from)

Copy the formula down the remaining column (click the little plus sign in bottom right of cell and drag it to desired end of column)

Next, copy the new data, do a cut and paste special. Choose the option for "values only"

This should put the data in as a date.

(I just verified that it worked so let me know if you have problems)

DaveT in NC
 

Metamorphic

Senior Member
Joined
Mar 15, 2008
Professional Status
Certified Residential Appraiser
State
California
Here's another way of doing it.

Excel holds dates as a number. IIRC the number starts with 1 at 1/1/1900 and increments 1 per day. So for instance if you put in 1/1/2007, then convert the format of that from a date to a number it will return 39083. If you type in the number 39084 and convert that number to a date it will return the date 1/2/07.

So if you convert your year column to a number, write a simple forumla to convert your month column to a number (= "number of months" *30.5). Then added the two columns together and converted back to date format, you'd have a combined field.
 

Terrel L. Shields

Elite Member
Gold Supporting Member
Joined
May 2, 2002
Professional Status
Certified General Appraiser
State
Arkansas
shoulda known who the real Guru is.
 
Status
Not open for further replies.
Find a Real Estate Appraiser - Enter Zip Code

Copyright © 2000-, AppraisersForum.com, All Rights Reserved
AppraisersForum.com is proudly hosted by the folks at
AppraiserSites.com
Top

AdBlock Detected

We get it, advertisements are annoying!

Sure, ad-blocking software does a great job at blocking ads, but it also blocks useful features of our website. For the best site experience please disable your AdBlocker.

I've Disabled AdBlock
No Thanks