Switching Parts of a String in Excel

I had the need to fix mistakenly entered email addresses in Excel, and luckily the mistake was a recognizable pattern. They should have been firstname.lastname@domain.com but were entered as lastname.firstname@domain.com. This short post explains how I extracted the names and switched them.

How to Swap Parts of an Excel String

First assume you have smith.joe@acme.com in cell A2, and that what you want is joe.smith@acme.com in cell A5. Enter this formula in cell A3:

=LEFT(A2,(FIND(".",A2,1)-1))

That extracts "smith" out of the email address in A2, by using the FIND function to return the number of the position of the period. You subtract 1 to tell LEFT when to stop extracting.

Next, in cell A4, enter this formula:

=MID(A2,(FIND(".",A2,1)+1),((FIND("@",A2,1))-(FIND(".",A2,1)+1)))

That uses MID to extract from one after the period, through one behind the @. This is about finding the positions of parts of the string you can see are in a pattern, and subtracting or adding 1.

Now in A5 you can concatenate the two in the proper order, and add the domain, thusly:

=A4&"."&A3&"@acme.com"

That's it & good luck!

Mama-chari Schrader Valve "Mushi Gomu" Repair

If you live in Japan or have visited, you will know the ubiquitous "mama chari" (mom bike) from its loud squeaky brakes, and precarious perching of bags and children both, on its front and rear platforms and baskets. My wife's mama-chari rear tire got a flat, and as the resident mechanic around the house, I got to fix it. I tried pumping it up, but that was not working, and noticed that to get the rear wheel off if I had to remove the tube, I would have to disassemble the rear friction brake (the source of all that squealing!).

Yikes, lots of parts. I decided to google removing the rear wheel on a mama-chari, and found an article talking about that topic, but warning readers to make sure the "mushi gomu" was intact, saying this is a common cause for what people think are flats. This mysterious "bug rubber" (虫ゴム) is just a little rubber tube about 1.5 mm diameter and 2 cm long, that you slide over the valve plug. It forms a seal between the metal valve plug and the side of the valve, letting air in during the pumping, but keeping it from flowing out otherwise. But, it makes like a flat if it is oxidized.

If you remove the valve cap, and unscrew / pull out the valve plug from the valve case sticking out of the hole in the rim, you can check whether the rubber is rotted or otherwise broken. Sure enough, when I took a look at ours, it was all rotted and falling off. So I went to my LBS (Local Bike Shop) and asked for "mushi gomu". JPY 150 and we were all fixed in 5 minutes.

Hope this helps other cycling DIYers in Japan.