Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Help!!! - remove character at position "X"
hi Folks,
I'm in desperate need of a macro that will traverse down an entire column and remove the seventh character... 12345678, will become 1234568 TIA!!! Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Help!!! - remove character at position "X"
Sub Remove7thCharacter()
Dim t_Range As Excel.Range Dim t_Col As Long Dim t_Rows As Long On Error GoTo Quit Set t_Range = ActiveWindow.Selection t_Col = t_Range.Rows.Column t_Rows = (ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1) For r = ActiveSheet.UsedRange.Row To t_Rows t_text = ActiveSheet.Cells(r, t_Col).Text If Len(t_text) 6 Then t_text = Left(t_text, 6) + Mid(t_text, 8) ActiveSheet.Cells(r, t_Col) = t_text End If Next Quit: End Sub "Stephen" wrote: hi Folks, I'm in desperate need of a macro that will traverse down an entire column and remove the seventh character... 12345678, will become 1234568 TIA!!! Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Help!!! - remove character at position "X"
Steve,
You could simple enter a formula like this =LEFT(A2,6)&MID(A2,8,LEN(A2)) into another column, copy down, then copy and paste values over the original. Either manually or with a macro would be simple... HTH, Bernie MS Excel MVP "Stephen" wrote in message ... hi Folks, I'm in desperate need of a macro that will traverse down an entire column and remove the seventh character... 12345678, will become 1234568 TIA!!! Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Help!!! - remove character at position "X"
Perfect, works like a charm!
Many Thanks!!! "jabbott" wrote: Sub Remove7thCharacter() Dim t_Range As Excel.Range Dim t_Col As Long Dim t_Rows As Long On Error GoTo Quit Set t_Range = ActiveWindow.Selection t_Col = t_Range.Rows.Column t_Rows = (ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1) For r = ActiveSheet.UsedRange.Row To t_Rows t_text = ActiveSheet.Cells(r, t_Col).Text If Len(t_text) 6 Then t_text = Left(t_text, 6) + Mid(t_text, 8) ActiveSheet.Cells(r, t_Col) = t_text End If Next Quit: End Sub "Stephen" wrote: hi Folks, I'm in desperate need of a macro that will traverse down an entire column and remove the seventh character... 12345678, will become 1234568 TIA!!! Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Help!!! - remove character at position "X"
Or a macro to do the same
Sub trimtosevenchar() mc = "k" On Error Resume Next For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row Set myc = Cells(i, mc) If Len(myc) 6 Then myc.Value = Left(myc, 6) & Mid(myc, Len(myc)) Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Steve, You could simple enter a formula like this =LEFT(A2,6)&MID(A2,8,LEN(A2)) into another column, copy down, then copy and paste values over the original. Either manually or with a macro would be simple... HTH, Bernie MS Excel MVP "Stephen" wrote in message ... hi Folks, I'm in desperate need of a macro that will traverse down an entire column and remove the seventh character... 12345678, will become 1234568 TIA!!! Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Help!!! - remove character at position "X"
You could simple enter a formula like this
=LEFT(A2,6)&MID(A2,8,LEN(A2)) into another column, copy down, then copy and paste values over the original. Either manually or with a macro would be simple... Another possibility... =REPLACE(A2,7,1,"") Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Change "-" from right first character to left first character to make a negative amount | Excel Programming | |||
Remove space and "-" character | Excel Programming | |||
remove last character in a column of part numbers if a "V" | Excel Discussion (Misc queries) |