0

I am a new user of Google Script and scripts in general. My company has Office licences and for strategics reasons it wants to use google services. My problem is that we extract from a software various data containing numbers. When we paste these datas on a spreadsheet the negatives numbers format is not recognized because they are like :

screenShot

I would like to apply the script only on a selection of active spreadsheet and the texte "1 234,56-" become a number "-1 234,56". The selection may contains positive number as "1 234,56".

Thank you for your help.

Best regards, Anthony.

Anthony MALBRANQUE
  • 3
  • 1
  • 3
  • Can you show us what script you have written and tried out, so that we can see why it may not be working? – Aung49 May 23 '19 at 07:19
  • I tried simple ReplaceInSheet(), it works for positives figures, they are converted into number. But on Ecel you can use * for this function, on GG Script it seems that no. I do not know how to write : to_replace : "*-" replace_with : "-*" – Anthony MALBRANQUE May 23 '19 at 07:52
  • You have yet to `accept` any answers to any question you've posted on StackOverflow. Please review how StackOverFlow works. – pgSystemTester Sep 18 '21 at 23:41

2 Answers2

0

if you're range is not too long you can try something like that :

How to replace text in Google Spreadsheet using App Scripts?

i have the same probleme for a file of 5k line to remplace "." by "," it work but need a bit time :)

i hope this will help you Best regards

Tonbouctout
  • 3
  • 1
0
=VALUE(REGEXREPLACE(REGEXREPLACE(TEXT(A1; "0000.00"); "\s"; ""); "(.*?)-"; "-$1"))

This will first convert the number to a text string, then remove the whitespace character, then move the - sign in front of the number, and lastly convert it back to a numeric value.

Before:

1 234,56-
352,90
2 342,89-
24,0
45,00-

After (and you can use Sheets' number formats to further alter if needed):

−1234,56
352,9
−2342,89
24
−45
sinaraheneba
  • 781
  • 4
  • 18
  • Hello, that is exactly what I need. I tried to type a script with REGEXREPLACE. I will try to integrate the formula into a script and let you know about the script i tried to create. – Anthony MALBRANQUE May 23 '19 at 12:15
  • Dear, I recently updated this formula by this one =IFERROR(VALUE(IF(OR(LEFT(A1;1)="-";RIGHT(A1;1)="-");"-";"")&REGEXREPLACE(TEXT(A1;"0000.00");"\W";""))/100;A1). Because finally, the number format should be 1,000,000.00 or 1 000 000,00 or 1.000.000,00... Thanks for you, I discovered the function REGEXREPLACE... Thanks again ! – Anthony MALBRANQUE Sep 17 '21 at 18:26
  • @AnthonyMALBRANQUE you should click `accept` then. – pgSystemTester Sep 18 '21 at 23:41
  • Dear, ok alright, that's accepted ! – Anthony MALBRANQUE Sep 21 '21 at 08:18