String to number / replace "." by nothing

[df]

Hello,

i have "sales" as V_String. To use math formulars i need to transform it to a numeric.

First Probem = How to transform the date? when i use select tool and change datatpye to double the 4.310 becomes 4.31 and this is wrong. i guess i need to replace the "." by nothing. but how? i tried the replacefunktion with...

word	replacement
.

... but i cant select replacement in the "find replace tool" since the row is empty.

second problem = how to use it on the whole data input? i dont want to select a specific row because i have a lot of sales columns.

my table looks like here:

company	sales week1	sales week2	...
A	13.543	42.583	...
B	12.456	4.310	...
C	...	...	...

[df]

Okay first Problem i can solve with "RegEx"

with regular Expression:
[.]

and leave the replacement Text empty

now i just need a methode to use it on more then one field

[danrh · Accepted solution]

I'm not positive that I understand the full requirement, but you can look into using the Multi-Field Formula tool to do the same change on multiple fields.  If you need to remove all of the "." then try this:

Note that I'm using the Select tool to convert to string - if your fields are already strings, you don't need it.  Hopefully this gets you moving in the right direction.

[df]

Thank you danrh,

your solution is correct

just finisched my own solution (but mine is not so easy)

1) RecordID for each row

2) transform the columns you need into rows - keyfield is recordid -> now the matrix (the spends in my case) are in one column (name: value) the headlines are in a other column (name: name)

3) use replacement like above for the new column (for value)

4) transform it back (group by recordid, company ....)

but ofcourse your solution is the better one and i will use it in my workflow.

[ilovecoding]

helped