Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?



Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks


Ship
Shiperton Henethe

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
external usenet poster
 
Posts: 10
Default How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?

On Apr 3, 1:41 pm, "ship" wrote:
Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks

ShipShipertonHenethe


I dont know what characters are going to be before or after the
number.
Basically I want to remove ALL characters apart from "0" to "9" and
"."
and then convert it into a numeric.

Any thoughts?


Ship








  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
external usenet poster
 
Posts: 3
Default How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?

A user defined function similar to the following will work

Public Function stripNumbers(rng As Range)

Dim i As Integer

For i = 1 To Len(rng.Value)

If Mid(rng.Value, i, 1) = "0" And Mid(rng.Value, i, 1) <= "9"
Then
strNum = strNum & Mid(rng.Value, i, 1)
End If

Next

stripNumbers = CDbl(strNum)

End Function

You can email me @ to request a copy.

Regards,
Eddie
http://www.ExcelHelp.us

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default How can I convert a character string that contains letters & numbe

http://www.ozgrid.com/VBA/ExtractNum.htm
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"ship" wrote:



Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks


Ship
Shiperton Henethe


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
external usenet poster
 
Posts: 5,651
Default How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric?

On 3 Apr 2007 05:51:16 -0700, "ship" wrote:

On Apr 3, 1:41 pm, "ship" wrote:
Hi

How do I convert a string that contains letters and numbers
into a number using Excel2003 (or ms Access)

e.g. "£2,456.99 (including flights)"

== needs to be converted into the the numeric 2456.99

With thanks

ShipShipertonHenethe


I dont know what characters are going to be before or after the
number.
Basically I want to remove ALL characters apart from "0" to "9" and
"."
and then convert it into a numeric.

Any thoughts?


Ship






With your string in A1, you could use this formula, courtesy of Bob Phillips:

=LOOKUP(9.99999999999999E+307,--MID(
A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&
"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
insert "-" as fourth character in number string cursednomore Excel Discussion (Misc queries) 2 February 7th 07 08:33 PM
Does the cell contain the character "-" within the string? Eric Excel Discussion (Misc queries) 4 December 3rd 06 04:46 PM
convert number to its letters ( convert "1" to "One" ) Fransois Excel Discussion (Misc queries) 1 September 23rd 06 11:58 AM
Excel: Changing "numeric $" to "text $" in a different cell. Heather_CCF Excel Worksheet Functions 1 September 5th 06 06:06 PM
remove last character in a column of part numbers if a "V" leo Excel Discussion (Misc queries) 3 January 18th 06 06:07 PM


All times are GMT +1. The time now is 09:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"