I want to convert

[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

 

[Domenic]

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!

 

[Chip Pearson]

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

 

[GerryK]

Try this,
=0+LEFT(A1,LEN(A1)-1)

 

[Frank Kabel]

Hi
you may use 'Data - Text to columns' and use the space as delimier. Or
use a function like
=--LEFT(A1,FIND(" ",A1)-1)

 

[Chip Pearson]

Try this,

=0+LEFT(A1,LEN(A1)-1)

This won't work if the word 'cost' is actually part of the string
to parse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Don Guillett]

How about a macro?

Sub numonly()
x = InStr(ActiveCell, " ")
y = InStr(x, ActiveCell, " ")
ActiveCell.Offset(0, 1) = Mid(ActiveCell, x + 1, y)
End Sub

 

[Domenic]

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.

 

[Arvi Laanemets]

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.

 

[Rossy77]

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

 

[Don Guillett]

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.

 

[Don Guillett]

I meant to say that it CAN replace the original column, if desired.