Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to make a certain amount of spaces be in a cell

0

I have a pivot table and loads of names with codes next to them but of course some are repeating because there are probably random extra spaces etc. I wondered if there was a fast way to get around this. Maybe by making every single cell have the same number of spaces. Then I would like everything to become upper case.

for example:

dave jim henrys 1233444567

dave  jim  henrys 1233444567

should be:

DAVE JIM HENRYS 1233444567

DAVE JIM HENRYS 1233444567

Can someone please tell me how to so that

excel
MS Office Version: bobjim11
posted by: bobjim11 (rep: 2) on Mon Jul 18, 2016 at 9:38 am
Answer
Discuss

Discussion

Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer and then click Yes to verify it.
don (rep: 1989) Jul 18, '16 at 11:21 am
I just did it :)
bobjim11 (rep: 2) Jul 19, '16 at 4:20 am
Add to Discussion

Answers

1
Selected Answer

To remove all extra spaces and make the data upper case use this formula:

=UPPER(TRIM(A1))

A1 should be the cell that contains the data and then just copy the formula down for the entire list/table.

Update

To replace the current list of data, go to the sheet that contains the list/table of data and put this formula into a column next to the data that you want upper case and then just copy the new data that looks how you want and paste it over the original data using Alt + E + S + V (copy/paste special).

posted by: don (rep: 1989) on Mon Jul 18, 2016 at 10:22 am
Discuss

Discussion

Where do I put this? To keep my heading in A1?
bobjim11 (rep: 2) Jul 18, '16 at 10:30 am
You edit your source data and not the actual pivottable. Go to the sheet that contains the list/table of data and put this formula into a column next to the data that you want upper case and then just copy the new data that looks how you want and paste it over the original data using Alt + E + S + V (copy/paste special).
don (rep: 1989) Jul 18, '16 at 10:41 am
Thank you for this, it was really helpful :D
bobjim11 (rep: 2) Jul 19, '16 at 4:21 am
Glad to help! :)
don (rep: 1989) Jul 19, '16 at 10:36 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login