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

 

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