I want to convert

  • Thread starter Thread starter Rossy77
  • Start date Start date
R

Rossy77

I have this string (a result of a web query)

Cost: 120000 D

and I want to copy this on another cell but in this way

120000


what do I have to do?
Thanks
Rossella
 
Hi Rosella,

If your string is in A1, try:

=LEFT(A1,FIND(" ",A1)-1)

If you have a column you'd like to convert and the strings are in column
A, then place the formula in B1 and copy it down as far as you need to.

Then, if you want to get rid of the old column, first:

1) select your range for your new column
2) copy > paste special > values
3) then, delete old column

Hope this helps!
 
Rossella,

Try a formula like the following, where the original data is in
A1.

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

You might also look at the Text To Columns tool from the Data
menu.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi
you may use 'Data - Text to columns' and use the space as delimier. Or
use a function like
=--LEFT(A1,FIND(" ",A1)-1)
 
How about a macro?

Sub numonly()
x = InStr(ActiveCell, " ")
y = InStr(x, ActiveCell, " ")
ActiveCell.Offset(0, 1) = Mid(ActiveCell, x + 1, y)
End Sub
 
Sorry! Didn't notice that "Cost" was part of the string. Of course,
then my formula won't work.

I guess I'm still not quite awake yet. :-)
 
Hi

Only this string ? :-)

You didn't give enough info for us to help you effectively!

Is "Cost: " always a part of string? When yes, then you can remove it using
SUBSTITUTE function. P.e. with your strin in A2:
=SUBSTITUTE(A2,"Cost: ","")
returns 120000 D

Now about tail part. When this is too always same, then you can use
SUBSTITUTE again, with result of first formula as source:
=SUBSTITUTE(SUBSTITUTE(A2,"Cost: ","")," D","")

When it is always a single character preceeded with space, then
=LEFT(SUBSTITUTE(A2,"Cost: ",""),LEN(SUBSTITUTE(A2,"Cost: ",""))-2)

When it can contain any number of characters, then probably you use MID
function to retrieve numeric part from result of substitute, determing the
lenght of it using FIND function and searching for space. And maybe you need
to check for absence of cpace (and characters) in string too.
 
Thanks Arvi!
Sorry if I've been short but I'm a beginner..you've been very kind!(unlike
italian users)
The right formula is
=SUBSTITUTE(SUBSTITUTE(A2,"Cost: ","")," D","")
It works!!!
Thanks for your excel lesson..
Rossella
 
It still requires another column which must then be converted to values and
then replace the original column. The macro solution replaces the original
column with what you desired.
 
Back
Top