example:
column a = i have a list of names and some of it in the column is with a space and i want to keep the rest of the names i have in this column to the same format like the first 6 names here
hi is there anyway to do this quicker instead of manual like or with a function or macro? i have over 200+ names and this is just an example... please help asap i need have until later today to finish this project...
sam
sim
simmy
sammy
bob
doe
tom
tim
timmy
tommy
I don't understand what you mean, Is it this:
=TRIM(A1) copied down, assuming your data start in A1.
Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Like Glenn said it sounds like you need the TRIM function
Run this to remove leading and trailing spaces
Please Login or Register to view this content.
Happy with my advice? Click on the * reputation button below
Re: how to backspace a text in a cell quicker
see the attached file. ty guys for the help...
So... =TRIM(A1) seems to do the job...
If there is only a single** word / name in each cell, as given in your example, then simply:-
-select the whole range that needs to be corrected
-press Ctrl h
-enter a single space into the first box
-leave the second box empty
-click replace all
** if there is more than 1 word / name in a single cell, something like " john doe" would become "johndoe" making this method unsuitable.
there is two words Tom Brady but then there is a space before the word " Tom Brady"and I want to get rid of that space and just keep "Tom Brady" but not "TomBrady" same for all the other names i have in my excel file...
regardless of how many words are in a cell i just want it to get rid of the first space it see " Joe bob doe" to just "Joe bob doe" etc....
thanks to everyone who replied and helped...please help here...
Last edited by SimonLee; 09-03-2016 at 04:26 AM.
Probably you did not tried the solution in #5 of Glenn Kennedy, since that will work for you added file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
The macro at this website will remove all leading/trailing
and multiple interspersed char 32 space characters.
It will also remove and/or convert char 160 non breaking
spaces into standard char 32 space characters. It will
work on text or numbers and the numbers will be
converted to true numeric numbers.
I use this macro dozens of times every single day!
It's a real time saver.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Use =TRIM(a1) and copy down.... for the 3rd time!!!!Originally Posted by SimonLee
okay i will try that can i put a range inside the ()
No. Apply it as shown. =Trim(A1) and copy down as far as needed.
okay i will try that can i put a range inside the ()?
okay. sorry for the repost accidentally click twice button since browser wasn't loading...
Simon,
If you have other data in column B then insert a new column B (temporarily), and then you should put Glenn's formula:
=TRIM(A1)
in cell B1. Then with cell B1 selected, you can double-click the fill handle, which is the small black square in the bottom right corner of the cursor, and this will automatically copy the formula down column B, making appropriate adjustments to the range in the formula, i.e. it will change to TRIM(A2), TRIM(A3) and so on in successive rows. Then you can convert the formulae to actual values in column B by highlighting all the cells, then clicking on < copy >, then right-click and choose Paste Special | Values | OK, and then pressing the < Esc > key. Then you can delete column A, and your sheet will look the same as before.
Hope this helps.
Pete
If you need to do this frequently the best option is one of the macros suggested.
I need to "clean" data I copy off the internet every day.
So, I put a button on my QAT and assigned the macro code to that button.
Now, when I need to "clean" some data it's as simple as selecting the data then clicking the button.
Done!
i tried to use =trim(a2) for that cell and it gives me the following errors...."there are one or more circular references where a formula refers to its own cell either directly or indirectly. ""this might cause them to calculate incorrectly. ""try removing or changing these references, or moving the formulas to different cells." please help i don't understand what is wrong here...
i also attached the file so u can help me fix thanks bros!
u can see from attached file that the rows that starts with season 2015 and week 17 to week 1 of season 2015 those player name column has a space before their first name i want to correct that and make it no space before their first name...and thats lots of names doing manually will take forever...
Last edited by SimonLee; 09-04-2016 at 12:18 AM.
For some reason, you had a blank row hidden at row 1604. I have removed it.
1. Go to first cell in an empty column (e.g. cell V1).
2. Enter =TRIM(A1)
3. Copy the formula all the way down the column (easiest by double-clicking the little square at the bottom right of cell V1).
4. The spaces have gone.
5. Select column V
6. Copy
7. Select Cell V1.
8. Paste VALUES (not just paste).
9. Delete column V.
10. Job done.
Sounds like you put this formula =trim(A2) in cell A2 . You need to put it in another cell to the one you're referencing.
Two solutions
1 create a new column anywhere on your sheet where you have available space and put the formula =trim(A2) in the second cell in this new column, drag formula down then copy all these cells and paste into column A as values (not formula)
2 use the macro I supplied in post #3
Follow my advice in post #16 and it'll take less than 1 second.
Done!
i dont see the formula in the excel file i download that was uploaded by u. those were the steps u took ? i have another file that is coming up with this error arggggggggg...is there a shortcut to find a hidden column or blank row?? probably have the same problem with this file but i just cant seem to find it...
i am getting the error after trying your first step here... after hitting enter it turns into a zero..is it also cause the data in the other column or the column width is not the same length...
From the image you posted it looks like you've created a new column A. his has shed all your columns to the right so old column A is now column B, old column B is now column C and so on.
That's OK but if you put this formula in column A you need to reference column B so in cell A2 you will put the formula =trim(B2) then drag that down to the bottom of your dataset.
Then copy and paste as values this column over the top of column B then delete column A
okay i finally got it to work after i copy and paste column a values into column b i notice the changed but it only worked for one of the row i dont know why....and does not look like the rows above cell b1044
Last edited by SimonLee; 09-05-2016 at 02:45 AM.
Don't post the image, post the excel file, without confidential information.
In the image we can't work, and most forummembers don't want to replicated your data.
as requested here's the file....
Last edited by SimonLee; 09-05-2016 at 04:14 AM.
i also got another problem on this file...column A. all the players names.. Example LarryFitzgerald I want to make it Larry Fitzgerald
how do i make all the players first and last name with a space? JohnBrown will be John Brown etc......
1) and where do we find the failure in your file.?
2) and where do we find the trim() formula in your file?
sorry i updated the file it has the trim formula but not working as it suppose to work...if u keep scrolling down column b some of the row will have a space and some don't...
The file you attached to Post #26, i.e. Players TE, has a leading "space" for the names in rows 27 to 503, but this is not a "normal" space - it has a character code of 160 instead of the usual 32, and is known as the non-breaking space character. The TRIM function will not work with this. Instead, you can use Find & Replace (CTRL-H) to change that character to nothing.
I've done this for you in the attached file, so you don't need column A anymore.
Hope this helps.
Pete
Using Advanced Filter, I have copied a unique list of your names in the file Players WR (Post #27) to column Z - there are 328 unique names. I have copied the names into column AA and begun to put a space between the forename and last name for the first four names - you will have to complete this task.
I have also inserted a new column B and put this formula in B2:
=VLOOKUP(A2,Z:AA,2,0)
which finds where the name in column A exists in column Z and replaces it with the adjusted name from column AA. Once you have completed putting the spaces in the names in column AA, you can copy this formula down to the bottom of column B, and all 6300+ names will be converted. You can then fix the values as I advised in Post #15, and then you can delete columns Z, AA and column A.
Hope this helps.
Pete
ahhhhhhhh ty so what did u input in the find and replace for the character code of 160??
There are two ways - in the Find What box you can hold down the Alt key and type 0160 on the numeric keypad and then release Alt, or you can copy the "space" from in front of one of those names and then paste it into the Find What box.
In both cases you would leave the Replace With box empty, and click Replace All.
Hope this helps.
Pete
In the Find what box...
Hold down the ALT key then, using the numeric keypad type 0160.
Replace with: nothing, leave this blank
Replace All
To automate the process of adding spaces, this UDF (User Defined Function) will do the job.
This has been added as a module in the file.Please Login or Register to view this content.
To get it to work =Addspaces(cellreference). I have used it in column AB and added spaces, as needed, to column AA.
REMEMBER: you will need to enable macros when you open this file.
Last edited by Glenn Kennedy; 09-05-2016 at 12:21 PM.
Please NOTE: You should use =TRIM(Addspaces(AA2)) - copied down.
I am on a VERY costly wifi and do not wish to re-upload your massive file. Here is a cut-down version.
Nice effect on row 149, Glenn - I'm not sure that you meant that !!
Pete
No. I had a quick scan up and down the column looking for IIs and IIIs but didn't spot any. Oh Pooh.
ty all for the help...i'll response back if i have any further problems...
If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you, and in your other threads). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks