Delete all characters after a set number in a cell

Hi all,

I have some cells that have the following data:

A1 = ABC/01/1234 Testing Stuff
A2 = ABS/09/1345 Other Stuff

I would like to remove the / and replace with a space which I have done by -

[VBA]
Sub removeText()
Selection.Replace "/", " "
End Sub
[/VBA]

This gives me:

A1 = ABC 01 1234 Testing Stuff
A2 = ABS 09 1345 Other Stuff

I now need to delete all after the final number.

I was thinking of using Len but never used it before as I am just learning. Any ideas are very welcome.

Regards

K
;)

Is there always 11 characters (including spaces) before the stuff at the end?

The Len function returns the length, the number of characters, in a string.

For the two examples you provided, you indicate that you want to keep the first 10 characters ("ABC 01 1234" is 10 characters log, including spaces).

You could use the Left function to grab the first 10 characters:
[VBA]Left("ABC 01 1234 Testing Stuff", 10)[/VBA]
which will return: "ABC 01 1234"

Now, using the Left function will get tricky if what you want ever exceeds 10 characters, because you will then need to determine how to identify only what you want, which shouldn't be too hard if your data is consitent.

Your original data shows as "ABC/01/1234 Testing Stuff", which shows the data you want with a space separating the rest of the entry. If this is constant, then you could easily identify just the first section of the data using the InStr function, which looks for the position of an identified string or character within a larger string.

So, you could use this to pull out just what you want:
[VBA]Left("ABC/01/1234 Testing Stuff", InStr(1, strtest, " ") - 1)[/VBA]

This will return "ABC/01/1234". The '-1' is there becase, without it, you would have an extra space at the end of your returned value.

Now you could use the Replace function to replace the / with a space.

Be sure to lookup the specifics on the functions in Help.

HTH:thumb

Worksheet formula
=SUBSTITUTE(LEFT(A1,FIND(" ",A1)),"/"," ")

Hi all, it will always be the 11.

Will check the details at work tomorrow.

Many thanks everyone.

K
;)

Hi all,

I am looking at using the cell that is highlighted rather than the actual characters.

I know I can do it with =Left(C1,10) but I am having trouble with the information that has been posted aready.

Is there a way that I can do that in VBA?

I may be barking up teh wrong tree, but I am learning

K
;)

Greetings Karti,

I think from looking at your first post you are referring to using the selection. Presuming you're not selecting a bajillion cells or anything (this code's not particularly efficient), would this work?

[vba]
Option Explicit

Sub removeText()
Dim rCell As Range
Dim LoopAgain As Boolean

Selection.Replace "/", " "
For Each rCell In Selection
Do
If Not Right(rCell.Value, 1) Like "#" Then
LoopAgain = True
rCell.Value = Left(rCell.Value, Len(rCell.Value) - 1)
Else
LoopAgain = False
End If
Loop While LoopAgain = True
Next
End Sub
[/vba]

Basically this strips characters from the right until it gets a number.

Mark

ACK! That would foul in case you had an empty cell...

[vba]
Sub removeText()
Dim rCell As Range
Dim LoopAgain As Boolean
Selection.Replace "/", " "
For Each rCell In Selection
Do
LoopAgain = False
If Len(rCell.Value) = 0 Then Exit Do

If Not Right(rCell.Value, 1) Like "#" Then
LoopAgain = True
rCell.Value = Left(rCell.Value, Len(rCell.Value) - 1)
Else
LoopAgain = False
End If
Loop While LoopAgain = True
Next
End Sub
[/vba]

[VBA]Sub Test_Keep11ReplaceSlash()
[A1] = "ABC/01/1234 Testing Stuff"
[A2] = "ABS/09/1345 Other Stuff"

[A4] = "Test"
[A1:A4].Select

Keep11ReplaceSlash
End Sub

Sub Keep11ReplaceSlash()
Dim cell As Range
For Each cell In Selection
If Len(cell.Value) > 10 Then cell.Value = Left(Replace(cell.Value, "/", " "), 11)
Next cell
End Sub[/VBA]

Hi,

[vba]Sub kTest()
Dim r As Long
Application.ScreenUpdating = 0
r = Range("a" & Rows.Count).End(xlUp).Row
Columns(1).Insert
With Range("a1")
.Resize(r).FormulaR1C1 = "=left(rc[1],find("" "",rc[1]&"" "")-1)"
.Offset(, 1).Resize(r).Value = .Resize(r).Value
End With
Columns(1).Delete
Application.ScreenUpdating = 1
End Sub[/vba]

HTH

Similar methodology to Krishna. We are told the string length so
[VBA]
Sub trims()
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
With rng
.Offset(, 2).FormulaR1C1 = "=left(RC[-2],11)"
.Offset(, 2).Copy
.PasteSpecial Paste:=xlPasteValues
.Offset(, 2).ClearContents
.Range("A1").Select
End With
End Sub
[/VBA]