How do I extract the numerical value of '325.00' to next column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pathung - 31 Jan 2008 20:36 GMT

Another software exported a list of values in the form of: '325.000',
complete with the apostrophies. How do I extract just the number itself to
the next column over?

Reply to this Message

PCLIVE - 31 Jan 2008 20:53 GMT

If your data is in A1, then one way:

=MID(A1,FIND("'",A1)+1,(FIND("'",A1,2)-1))

If the number of digits is the same for all the numbers, then you could use:
=MID(A1,2,8)

Another way:

=MID(A1,2,(LEN(A1)-2))

> Another software exported a list of values in the form of: '325.000',
> complete with the apostrophies. How do I extract just the number itself to
> the next column over?

Reply to this Message

Bernard Liengme - 31 Jan 2008 22:09 GMT

=--LEFT(E19,LEN(A1)-1)
since the first ' is generally ignored by Excel
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Another software exported a list of values in the form of: '325.000',
> complete with the apostrophies. How do I extract just the number itself to
> the next column over?

Reply to this Message

Ron Coderre - 31 Jan 2008 22:34 GMT

You may want to try this:

Select your one-column range of "numbers"

From the Excel Main Menu:
<data><text-to-columns>
...Check: Delimited......Click [Next]
...Check: Other....Enter an apostrophe (')
...Click [Next]
...Select the 2nd column......Check: Do not Import
...Destination: (choose the cell to the right of the current data)
...Click [Finish]

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> Another software exported a list of values in the form of: '325.000',
> complete with the apostrophies. How do I extract just the number itself to
> the next column over?

Reply to this Message