Formatting Numbers

A while back I was asked how to make all phone numbers in a list the same format. I don't have a very good solution, but I have something.

The first idea I had was to do data validation in the Google Form where you collect the data. Getting data as pure as you can when you collect it is always the best.

But the information had already been collected.

Step 1

The first thing I wanted to do was to see if I could get a list of random numbers. Fortunately, Google Sheets has a RAND function that will do just that.

Step 2

Step 2 was to copy the numbers without formatting. When you paste into Google Sheets, there is a little clipboard icon that shows up. Clicking that will give you the option for a menu where you can choose to paste the values or the formatting. I chose the values because I didn't want the function anymore. Besides being good practice, the RAND function would have kept changing the data.

Step 3

The next thing I did was to reformat the numbers. I went to format > number > custom number format. Then I put in the formatting for a Japanese cell phone. (In my example another recommendation was to make a separate field in the form for a home telephone number that has a different formatting.)

Make an Escape Room Activity with Google Sheets

 

Click to make a copy

I saw a Google sheet that had math problems and if you got the problems right it revealed the image. I was tempted to buy the sheet on TPT, but I thought I could do it myself.

In my first attempt a few weeks ago, I didn't get anything to work. 

But after a few hours this morning, I got it. Here is my flow.

Steps to make it
1. First I searched for [google sheets reveal image]. 

2. I found this site https://sheetshelp.com/image/

3. I scanned the article and went to the comments. 

4. The first comment talks about basing an image on another cell and the author's reply gave a link to a support document.

5. The support document was for the function IFS (which means two IF statements) but I wanted one. So I went to the support page for the IF function.

6. I got the syntax for the IF function.

7. I found an image I could use. I wasn't picky so I went to Pixabay and looked for an open picture of a robot. 

8. I thought I could cut it into 9 pieces using GIMP (open source Photoshop), so I searched for something like [cut image into equal parts GIMP].

9. Then I looked for an online editing site that could do it for me.

10. I found one and cut my image into 9 parts.

11. Then I uploaded my 9 images to Google Drive.

12. I put the images in a folder on Google Drive and tried to link to them.

13. Despite changing the link settings, it didn't work. It also didn't work on Dropbox and Box. None of the three give a URL with the ending being .png or .jpg.

14. I published the images to a Blogger post and copied the image URL.

15. I did a test of the IF function.

16. I got it to work with both TRUE and FALSE values.

17. Then I tried it with an image. But it didn't work.

18. It didn't work because I had the equal sign in front of IMAGE. I fixed it and got: =IF(A3=1,IMAGE("https://i.creativecommons.org/l/by/4.0/88x31.png"),"green")

19. With the images working in the formula, I added my own images.

20. They were not cut right so I had to go back to the online image site and redo them.

21. The robot was not lining up so I had to make those three columns narrower.

22. The sheet was too busy so I made a new sheet and cleaned it up.

I can see using this as a digital breakout room and it can be used in any subject. I suggested to my wife that she use it in her Japanese class. She could start with just having the students complete the sheet, then each time you did it, show them how you made it little by little. She teaches HS, so they could totally make something like this.

Click the above image for a template of my work.