Formula Assistance in Excel xlsx for Office 2010 w/ Windows 7

10 pts.
Tags:
Excel 2010
Excel Forms
Excel formulas
office 2010
Windows 7
I need to know which formula I can use that will produce the following result with the following existing data:

Column A, Row 2 - (Cell A2) - Value is 200806140804

Column A, Row 3 - (Cell A3) - Value is 200806150805

Column A, Row 4 - (Cell A4) - Value is 200806160806

 

Is there a formula I can enter in Column B, Rows 2, 3, 4 that will allow me to drop the last 4 digits for each of the values?

My data is much larger than 3 rows, but for simplicity, I narrowed my question down to 3.

Thank you so much!



Software/Hardware used:
Office 2010
ASKED: September 3, 2010  4:41 PM
UPDATED: September 8, 2010  2:21 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could use the <a href=”http://office.microsoft.com/en-us/excel-help/left-leftb-HP005209153.aspx?CTT=5&origin=HP005203209″>LEFT function</a>:

<pre>B2 =LEFT(A2,LEN(A2)-4)</pre>

If the values in column A have a fixed length, you could do something like this:

<pre>B2 =LEFT(A2,8)</pre>
********************************************
There is more than one way to skin a cat (I always wonder, if it is true for dog or hamster!).
I liked the solution already provided. Here is another way:

Define the cell range as Numeric with 0 decimal point.

On Cell B2 use =Trunc(A2/10000) Note: Trunc is needed to avoid rounding.

Since you mentioned you have a large worksheet – you may use the Auto fill option by dragging Fill handle.
Good luck.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Mommiek
    [...] here to read the rest: Formula Assistance in Excel xlsx for Office 2010 w/ Windows 7 AKPC_IDS += "34472,";Popularity: unranked [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following