Any Excel Gurus out there?
I have some data in a single column. (Couple of thousand rows....)
I would like to be able to remove any "spaces" either before or after the actual content. Is this possible using VB or something? I would also like to remove "*" from after say xyz123* and put it into the cell on the right of the original? (it is an actual * as opposed to wild character) Again VB or something? Thanks for any help in advance. Buck |
assuming data is in column 1 and you want the stars moving to 2 put this in a macro:
Sub StarsNSpaces() Dim x As Integer, s As String For x = 1 To ActiveSheet.UsedRange.Rows.Count s = Trim(CStr(Cells(x, 1))) If Right(s, 1) = "*" Then Cells(x, 1) = Mid(s, 1, Len(s) - 1) Cells(x, 2) = "*" End If Next x End Sub if you need to change it, change the start value of x (for x=1) to be the row to start processing and change the reference to cells to be the correct column : A=1, B=2, C=3 etc. |
Can't you just export it as text and read it back in using * as delimiter?
Seems like this would be a much more efficient way of achieving your requirements. |
Thanks Eagle7 that was just the job.
Sheepslipper, I think you are right, but in my specific case, could have been a bit tricky with the other data that is present. Eagle7's macro does the job (thanks to copy and paste) and Eagle7's macro wisdom! Cheers anyway. Buck |
If you do a lot of complex Excel stuff, try this free set of tools from ASAP
http://www.asap-utilities.com/ - I found some brillliant stuff in there... Cheers Mick :D |
you can do both without using vba.
To remove outer (left and right) spaces use excel trim function e.g. '=trim(a1)' To split the column, select the cells to split (or the whole column) then menu Data>Text To Columns and put in * as the delimiter easy peasy pudding and pie ;) :D |
All times are GMT +1. The time now is 02:49 PM. |
© 2024 MH Sub I, LLC dba Internet Brands