1

I have to copy/paste many data from pdf to excel. Unfortunately, the format is always in text and I can't convert it despite my formula.

Here is the example :

enter image description here

Do you have an idea to handle this?

Thanks!

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
lfilleur
  • 49
  • 1
  • 5
  • 1
    Try putting a double unary between the = and the rest of the formula. –  Mar 04 '19 at 08:54
  • Sometimes doing *1 as the final step works as well... – Solar Mike Mar 04 '19 at 08:58
  • I tried like this : =--(SUBSTITUE(SUBSTITUE(C2;CAR(160);"");" €";"")) but it doesnt work. I've also tried the CNUM and end the formula with *1 – lfilleur Mar 04 '19 at 09:01
  • Just in case you could use an UDF, check https://stackoverflow.com/a/7239408/9199828. It will extract all numbers. Your quantities have 2 decimals. Both will be extracted as integers, so after extracting all numbers, divide by 100 to get the decimal part. – Foxfire And Burns And Burns Mar 04 '19 at 09:38

1 Answers1

0

You also have to consider space after first two digit 16 000,00 €. So, substitute space as well. Comma and semicolon depends on region.

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),"€","")," ","")

Edit:

If space is not regular space in case of Char(160) then use.

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),"€",""),CHAR(160),"")

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • The op's sample seemed to be using a non-breaking space (CHAR(160)) and not a normal space (CHAR(32) or `" "`). The comma seems to be a non-EN-US decimal point. –  Mar 04 '19 at 09:11
  • Then have to use `Char(160)` instead of regular space like `=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),"€",""),CHAR(160),"")` – Harun24hr Mar 04 '19 at 09:15