Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How replace a character in a string of characters?
Anyone know how to pick out a digit in a string of characters like
this? =---FS- 12345 I am trying to replace the first character and my code keeps coming back with "type mismatch error 13". My code is simple (but wrong). I am trying to get rid of the = character and replace it with a ' . Thx Chet Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD() MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row For Rowy = 2 To MaxRow Target = Left(Cells(Rowy, 11), 1) If Target = "=" Then Target = "'" Left(Cells(Rowy, 11), 1) = Target Next Rowy End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How replace a character in a string of characters?
Hi Chet,
try this revised version Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD() MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row For rowy = 2 To MaxRow Target = Left(Cells(rowy, 11), 1) If Target = "=" Then Cells(rowy, 11) = Mid(Cells(rowy, 11), 2) End If Next rowy End Sub I think your code was failing because you can only use the LEFT function for reading a value not for writing. Cath |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How replace a character in a string of characters?
Hi Cath,
Thanks for your help! I should have mentioned my code is failing at the Target = Left(Cells(rowy, 11), 1) line. It still is failing at that point. Any other ideas? :) Thx Chet wrote: Hi Chet, try this revised version Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD() MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row For rowy = 2 To MaxRow Target = Left(Cells(rowy, 11), 1) If Target = "=" Then Cells(rowy, 11) = Mid(Cells(rowy, 11), 2) End If Next rowy End Sub I think your code was failing because you can only use the LEFT function for reading a value not for writing. Cath |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How replace a character in a string of characters?
Chet,
AH! it seems your cells that start with = do really contain formulae. I had assumed there was a quote before the = to make them strings. In which case try this. Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD() Dim Target As String MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row For rowy = 2 To MaxRow Target = Cells(rowy, 11).Formula If Left(Target, 1) = "=" Then Cells(rowy, 11) = Mid(Target, 2) End If Next rowy End Sub Cath |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How replace a character in a string of characters?
Ahh.. this makes sense... I will try it and see how it works.. Thx VERY
much. ! :) Chet wrote: Chet, AH! it seems your cells that start with = do really contain formulae. I had assumed there was a quote before the = to make them strings. In which case try this. Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD() Dim Target As String MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row For rowy = 2 To MaxRow Target = Cells(rowy, 11).Formula If Left(Target, 1) = "=" Then Cells(rowy, 11) = Mid(Target, 2) End If Next rowy End Sub Cath |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How replace a character in a string of characters?
Hi
You could try this. It will remove the = sign and replace with the ' text symbol. However this results in the formula appearing in the cell ie 'A2+B2 not the whole number. If this is what you want this should do the trick. Sub Remove() Dim StartRng As Range Set StartRng = Range("a1", Range("A65536").End(xlUp)) For Each cell In StartRng StartRng.Replace what:="=", Replacement:="'", LookAt:=xlPart Next End Sub wrote: Chet, AH! it seems your cells that start with = do really contain formulae. I had assumed there was a quote before the = to make them strings. In which case try this. Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD() Dim Target As String MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row For rowy = 2 To MaxRow Target = Cells(rowy, 11).Formula If Left(Target, 1) = "=" Then Cells(rowy, 11) = Mid(Target, 2) End If Next rowy End Sub Cath |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How replace a character in a string of characters?
Chet,
Not sure if you actually have a formula or text in your cells, but... The default property of a Range is .Value. So that is what you are working with here, as you do not specify a property in your code. There is also the .Formula and .HasFormula which may be what you need instead, if formulae are involved. For Rowy = 2 To MaxRow with Cells(Rowy, 11) If .HasFormula = True Then .value = "'" & .formula end with Next Rowy NickHK "Chet" wrote in message oups.com... Anyone know how to pick out a digit in a string of characters like this? =---FS- 12345 I am trying to replace the first character and my code keeps coming back with "type mismatch error 13". My code is simple (but wrong). I am trying to get rid of the = character and replace it with a ' . Thx Chet Sub REMOVE_INVALID_DATA_PHX_DOWNLOAD() MaxRow = ActiveSheet.Range("A65536").End(xlUp).Row For Rowy = 2 To MaxRow Target = Left(Cells(Rowy, 11), 1) If Target = "=" Then Target = "'" Left(Cells(Rowy, 11), 1) = Target Next Rowy End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace character in string, not document | Excel Programming | |||
Extracting a character from a string of characters | Excel Discussion (Misc queries) | |||
Remove all characters following the first character in a string | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
How do I replace * as a character in a string in Excel? | Excel Programming |