String copy and conversion Hi, In my Excel sheet, I have cells B10 through B73 s
ID: 3561095 • Letter: S
Question
String copy and conversion
Hi,
In my Excel sheet, I have cells B10 through B73 some mixed numbers with characters such as "123 czk", "45 huf" or "183 PLN". What I need is a formula that can do the following:
1-First copy these values to the corresponding cells in the "G" column. For instance if cell B:23 has a value of "345 czk" this get copied to cell G:23
2-Take the content of the cells in B10:B73, strip the characters and blank then multiply the number by 0.45 for columns with czk, 0.23 for columns with PLN and so on then replace the cell value with the resulting number. For instance if cell B65 has a value of "203 czk", it should be replaced with decimal number of 91.35 which is (203*0.45).
Thank in advance for the any help. If you can give me the formula, that would be the best.
Explanation / Answer
^You cannot copy cells with a formula. Y&ou can create a duplicate of the value in another cell, e.g. in G23: =B23 but any change in B23 will be reflected in G23.<
You can neither put a formula and a value in a cell. You have to use a helper column, let's say C:
In C10: =LEFT(B10,FIND(" ",B10)-1)
to get the numerical value.
To get the string, you can use =MID(B10,FIND(" ",B10)+1,3)
To find the multiplier, set up a table like this:
czk .45
pln .23
huf .11
Let's say that we have this table in Sheet2 A1:B3.%#$
Our formula in C10 becomes now:
=LEFT(B10,FIND(" ",B10)-1)*VLOOKUP(MID(B10,FIND(" ",B10)+1,3),Sheet2!$A$1:$B$3,2,FALSE)
and drag down to B73.
Another formula to get the same result but without a helper table:
=LEFT(B10,FIND(" ",B10)-1)*LOOKUP(MID(B10,FIND(" ",B10)+1,3),{"czk";"huf";"pln"},{0.45;0.11;0.23})
Should you have empty cells in the range B10:B73, then you need to catch the error:
=IFERROR(LEFT(B11,FIND(" ",B11)-1)*LOOKUP(MID(B11,FIND(" ",B11)+1,3),{"czk";"huf";"pln"},{0.45;0.11;0.23}),"")
If you only need the total sum of all values, then you can do it in 1 formula:
=SUMPRODUCT(--LEFT(B10:B12,FIND(" ",B10:B12)-1)*LOOKUP(MID(B10:B12,FIND(" ",B10:B12)+1,3),{"czk";"huf";"pln"},{0.45;0.11;0.23}))
NOTE: depending on your regional settings you may need to replace the ";" by a ",", e.g.
=LEFT(B10,FIND(" ",B10)-1)*LOOKUP(MID(B10,FIND(" ",B10)+1,3),{"czk","huf","pln"},{0.45,0.11,0.23})