Change (or replace) a String to Different Format

[Tash Point O]

Hello!

I have recorded a macro to find an exact string and replace it to a different format. However, I have different strings and in the macro I would have to put in all the different variations in order for the macro to work. Is there another way I can change various strings to a certain format. Here is the example of what the heck I'm talking about. I need to change Column A's format to Column B's format.

[/IMG]


Here is the macro I recorded, as you can see, I'd have to put in every instance for strings from Column A:

[/IMG]

 

[VBA Geek]

you can use a simple formula: =RIGHT(A1,4)&"_"&LEFT(A1,1)&MID(A1,5,1)

 

[Tash Point O]

you can use a simple formula: =RIGHT(A1,4)&"_"&LEFT(A1,1)&MID(A1,5,1)

Wow I feel like a doofus. This worked, thank you very much

 

[Scott Huish]

Or since you're always dealing with quarters

=RIGHT(A1,4)&"_"&LEFT(A1)&"Q"

 

[Eric W]

Try the Flash Fill tool. In your example above, you have your data in column A. Put 1 or 2 samples of how you want the output to look, say in B2. Select B2, go to the Data tab, and click on the Flash Fill button (or just press Ctrl-E). It should fill the rest of the rows in column B automatically.

 

[Tash Point O]

You guys are seriously awesome. So thankful for this forum.

 

[Tash Point O]

Try the Flash Fill tool. In your example above, you have your data in column A. Put 1 or 2 samples of how you want the output to look, say in B2. Select B2, go to the Data tab, and click on the Flash Fill button (or just press Ctrl-E). It should fill the rest of the rows in column B automatically.

Yes! this works!

 

[Tash Point O]

Or since you're always dealing with quarters

=RIGHT(A1,4)&"_"&LEFT(A1)&"Q"

Awesome this works perfectly!