Any Excel Gurus out there?
#1
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
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
#2
Scooby Regular
Join Date: Jan 2002
Location: Plymouth
Posts: 960
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
#4
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
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
#6
Scooby Regular
Join Date: Sep 2001
Location: A powerslide near you
Posts: 10,261
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM
bluebullet29
General Technical
9
05 October 2015 02:17 PM