Erase part of text on cell's ?
| Author |
Message |
|
LMC
#1 / 12
|
 Erase part of text on cell's ? Hello, I have a long list of user names and I would like to erase the names by keeping only the initial letter. For example: Original : Tim William Final : T W It is possible to do that? Thank's for the answer
LLOYS
|
| Fri, 23 Jul 2004 18:00:59 GMT |
|
 |
|
LMC
#2 / 12
|
Erase part of text on cell's ? Sorry, I forgot to mention that I would like to erase the text by an automatic process for example. Thanks for your help,
LLOYS
Quote: > Hello, > I have a long list of user names and I would like to erase the names by > keeping only the initial letter. > For example: > Original : > Tim > William > Final : > T > W > It is possible to do that? Thank's for the answer
> LLOYS
|
| Fri, 23 Jul 2004 18:24:23 GMT |
|
|
|
#3 / 12
|
Erase part of text on cell's ? Try this: Public Sub test()
Dim sValue As String
While ActiveCell.Value <> ""
sValue = ActiveCell.Value
sValue = Mid(sValue, 1, 1)
ActiveCell.Value = sValue
ActiveCell.Offset(1, 0).Select
Wend
End Sub
Quote:
> Hello, > I have a long list of user names and I would like to erase the names by > keeping only the initial letter. > For example: > Original : > Tim > William > Final : > T > W > It is possible to do that? Thank's for the answer
> LLOYS
|
| Fri, 23 Jul 2004 18:28:00 GMT |
|
|
|
Tfelt Jean-Yve
#4 / 12
|
Erase part of text on cell's ? Hi, You can simply use the "Left" function (Excel or VBA) Excel : In another column insert function "=left(cell ref you select, 1)", where "1" is the amount of characters you want to extract from the string.Thereafter, filldown . Regards Jean-Yves Quote:
> Hello,
> I have a long list of user names and I would like to erase the names by
> keeping only the initial letter.
> For example:
> Original :
> Tim
> William
> Final :
> T
> W
> It is possible to do that? Thank's for the answer
> LLOYS
|
| Fri, 23 Jul 2004 18:35:18 GMT |
|
|
|
Tfelt Jean-Yve
#5 / 12
|
Erase part of text on cell's ? Hi again, In that case : Assuming your name list start in A1 and down Remark: No undo available, I would keep a copy of the original data. Sub ReplaceNamebyInitial()
Dim cl As Range
For Each cl In Range("A1", Range("A1").End(xlDown))
cl = Left(cl, 1)
Next
End Sub
Regards,
Jean-Yves
Quote:
> Sorry,
> I forgot to mention that I would like to erase the text by an automatic
> process for example.
> Thanks for your help,
> LLOYS
> > Hello,
> > I have a long list of user names and I would like to erase the names by
> > keeping only the initial letter.
> > For example:
> > Original :
> > Tim
> > William
> > Final :
> > T
> > W
> > It is possible to do that? Thank's for the answer
> > LLOYS
|
| Fri, 23 Jul 2004 18:53:04 GMT |
|
|
|
LMC
#6 / 12
|
Erase part of text on cell's ?
Answer to Jean-Yves
Unfortunately the solution proposed by Jean-Yves doesn't work for me, an
error message appear.
Will be possible to give a concrete example?
Many thanks again,
LLOYS
Quote: > Try this: > Public Sub test()
> Dim sValue As String
> While ActiveCell.Value <> ""
> sValue = ActiveCell.Value
> sValue = Mid(sValue, 1, 1)
> ActiveCell.Value = sValue
> ActiveCell.Offset(1, 0).Select
> Wend
> End Sub
> > Hello,
> > I have a long list of user names and I would like to erase the names by
> > keeping only the initial letter.
> > For example:
> > Original :
> > Tim
> > William
> > Final :
> > T
> > W
> > It is possible to do that? Thank's for the answer
> > LLOYS
|
| Fri, 23 Jul 2004 19:22:21 GMT |
|
|
|
Tfelt Jean-Yve
#7 / 12
|
Erase part of text on cell's ? Hi LMC, Assume you have "Monday" in A1, fill down up upto sunday. The sub i gave you will replace the content from A1 to A7 (the last cell in the same column) by the first character
position of the string to extract, whereas I use the Left function. PS When you have error messages, please forward them. Regards Jean-Yves Quote:
> Answer to Jean-Yves
> Unfortunately the solution proposed by Jean-Yves doesn't work for me, an
> error message appear.
> Will be possible to give a concrete example?
> Many thanks again,
> LLOYS
> > Try this:
> > Public Sub test()
> > Dim sValue As String
> > While ActiveCell.Value <> ""
> > sValue = ActiveCell.Value
> > sValue = Mid(sValue, 1, 1)
> > ActiveCell.Value = sValue
> > ActiveCell.Offset(1, 0).Select
> > Wend
> > End Sub
> > > Hello,
> > > I have a long list of user names and I would like to erase the names by
> > > keeping only the initial letter.
> > > For example:
> > > Original :
> > > Tim
> > > William
> > > Final :
> > > T
> > > W
> > > It is possible to do that? Thank's for the answer
> > > LLOYS
|
| Fri, 23 Jul 2004 19:35:44 GMT |
|
|
|
LMC
#8 / 12
|
Erase part of text on cell's ? Hi Jean-Yves, Thanks for your great help, everithing is working fine {^_^}
LMC
Quote: > Hi again, > In that case : > Assuming your name list start in A1 and down > Remark: No undo available, I would keep a copy of the original data. > Sub ReplaceNamebyInitial()
> Dim cl As Range
> For Each cl In Range("A1", Range("A1").End(xlDown))
> cl = Left(cl, 1)
> Next
> End Sub
> Regards,
> Jean-Yves
> > Sorry,
> > I forgot to mention that I would like to erase the text by an automatic
> > process for example.
> > Thanks for your help,
> > LLOYS
> > > Hello,
> > > I have a long list of user names and I would like to erase the names
by
> > > keeping only the initial letter.
> > > For example:
> > > Original :
> > > Tim
> > > William
> > > Final :
> > > T
> > > W
> > > It is possible to do that? Thank's for the answer
> > > LLOYS
|
| Fri, 23 Jul 2004 23:02:00 GMT |
|
|
|
Dave Peterso
#9 / 12
|
Erase part of text on cell's ? Just one more option: Sub testme3()
Dim rng As Range
'set your range to whatever you want
Set rng = Range("a1", Cells(Rows.Count, "a").End(xlUp))
If Application.CountA(rng) > 0 Then
rng.TextToColumns Destination:=rng(1), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 9))
Else
MsgBox "none found"
End If
End Sub
It uses the Data|Text to Columns stuff if you were doing it by hand.
Quote:
> Hello,
> I have a long list of user names and I would like to erase the names by
> keeping only the initial letter.
> For example:
> Original :
> Tim
> William
> Final :
> T
> W
> It is possible to do that? Thank's for the answer
> LLOYS
-- Dave Peterson
|
| Sat, 24 Jul 2004 08:35:26 GMT |
|
| |
|
