Delete First X Characters In Cells

edelauna · Oct 9th 2007

Hello, I am trying to write a macro that will go through each cell in a column with the following format "| 12- 4" or "| 60-11" and will remove the first to character "| ".

There numbers are lengths, the first being feet the second inches. I would like to achieve in a separate column the numerical length (with decimals) multiplied by 1%. My code is as follows I just need help on the conversion.

Code

Sub newLength()
    Dim LR, lrow As Integer
    Dim feet, inches, Line As Double
    
    LR = Range("B65536").End(xlUp).Offset(1, 0).Row
    For lrow = LR To 2 Step -1
        If Cells(lrow, "D") <> Cells(lrow - 1, "D") Then
           Rows(lrow).Insert Shift:=xlDown
        End If
            feet = Left(Cells(lrow, "E").Value, 4)
            inches = Right(Cells(lrow, "E").Value, 2) / 12 'I need to debug this line, but I suspect there is more I need to do.
            Cells(lrow, "F") = feet + inches
    Next lrow
End Sub

Display More

**StephenR** · Oct 9th 2007

Re: Macro To Delete The First Two Characters Of A Cell

To remove the first two characters:

Code

LEFT(RANGE("A1"),LEN(RANGE("A1"))-2)

p45cal · Oct 9th 2007

Re: Macro To Delete The First Two Characters Of A Cell

Instead of

Code

feet = Left(Cells(lrow, "E").Value, 4) 
        inches = Right(Cells(lrow, "E").Value, 2) / 12 'I need to debug this line, but I suspect there is more I need to do.
        Cells(lrow, "F") = feet + inches

try

Code

x = Split(Mid(Cells(lrow, "E").Value, 3), "-")
Cells(lrow, "F") = x(0) + x(1) / 12

but I'm not sure what you mean by "multiplied by 1%"
p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from StephenR
To remove the first two characters:
Code
LEFT(RANGE("A1"),LEN(RANGE("A1"))-2)

which nicely removes the last two characters! Substitute 'LEFT' with 'RIGHT' and you're getting somewhere, but

Code

Mid(Range("A1"),3)

does it too.:smile:

edelauna · Oct 9th 2007

Re: Macro To Delete The First Two Characters Of A Cell

what does mid and split do? My other problem is there is a double digit I would only like to delete one character, is there some way to make the characters I want a variable?

Code

x = Split(Mid(Cells(lrow, "E").Value, 3), "-")
Cells(lrow, "F") = x(0) + x(1) / 12

This code does not work?

This is what I ended up doing but I run into an error

Code

Sub newLength()
    Dim LR As Integer
    Dim feet, inches, Line As Double
    Dim r As Range
    Dim iRow As Long, lRow As Long
    Dim s As String
    Dim iChr As Integer
    Dim nDelChr As Long, nDelCR As Long, nDelRow As Long, nDelSp As Long
    Const sFmt As String = "#,##0"
    Dim aiStr() As String ' dimensions as strings
    Dim dDim() As Double ' dimensions as numbers
    Dim iDim As Integer ' index to dimensions
    Dim nDim As Integer ' number of dimensions
    
    LR = Range("B65536").End(xlUp).Row
        
    For lRow = LR To 2 Step -1
      
        ' get the dimensions as substrings
        aiStr = Split(Cells(lRow, 6), " ")
        nDim = UBound(aiStr) + 1
        ReDim dDim(1 To nDim) ' *ERROR* say subscript is out of range
        For iDim = 1 To nDim
            dDim(iDim) = Val(aiStr(iDim - 1))
        Next
        Cells(lRow, 7) = (dDim(1) + (dDim(2) / 12)) * 1.01
        If Cells(lRow, "E") <> Cells(lRow - 1, "E") Then
          ' Rows(lRow).Insert Shift:=xlDown
           'Rows(lRow).Insert Shift:=xlDown
        End If
    Next lRow
    


End Sub

Display More

Any advice on to how to resolve this error?

p45cal · Oct 9th 2007

Re: Macro To Delete The First Two Characters Of A Cell

Quote from edelauna

what does mid and split do?

Copying the code, putting the cursor on the appropriate word and pressing F1 gets you an answer much faster than posting this sort of question here, however, from Help:
[COLOR="Gray"]Mid Function

Returns a Variant (String) containing a specified number of characters from a string.

Syntax

Mid(string, start[, length])

The Mid function syntax has these named arguments:

string Required. String expression from which characters are returned. If string contains Null, Null is returned.
start Required; Long. Character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string ("").
length Optional; Variant (Long). Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.[/COLOR]

In this case it effectively removes the first 2 characters from a string, start being 3.

Again from Help:
[COLOR="Gray"]Split Function

Returns a zero-based, one-dimensional array containing a specified number of substrings.

Syntax

Split(expression[, delimiter[, limit[, compare]]])

The Split function syntax has these named arguments:

expression Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
[/COLOR]
The expression above is, in this case, the result of the Mid function while the delimiter is "-", the minus sign.

Quote from edelauna

My other problem is there is a double digit I would only like to delete one character?

The strings you supplied both had double digits. Could you be specific about what you mean here please. Which double digits do you mean? Which single character do you want deleted. I am neither a mind reader, nor can I see over your shoulder.

Quote from edelauna

.. is there some way to make the characters I want a variable?

Well, the result of the Split function is to result in an array with 2 members, x(0) and x(1), being (in the case of the string you supplied "| 12- 4") "12" and " 4" respectively. So you have your variables.

Quote from edelauna
Code
x = Split(Mid(Cells(lrow, "E").Value, 3), "-")
Cells(lrow, "F") = x(0) + x(1) / 12
This code does not work?

I don't understand the question mark at the end. Have you tried the code? It certainly worked for the two strings you supplied, yielding 12.3333333 and 60.91666667

p45cal

**StephenR** · Oct 9th 2007

Re: Macro To Delete The First Two Characters Of A Cell

p45cal: thanks for spotting my deliberate mistake! Good to see you're on the ball.

edelauna: all very confusing. Which columns are you using? You're looking for the last row in column B (LR), and then you're looking at column F (aiStr = Split(Cells(lRow, 6), " ")). If that's the relevant column you need to amend p45cal's code.

**Reafidy** · Oct 10th 2007

Re: Macro To Delete The First Two Characters Of A Cell

Edelauna,

Have a look at the code below and study it carefully. You may need to alter columns etc.

If it is not what you need then please take some time to cleary re-explain your problem and more importantly what you are trying to do. Perhaps without any code for clarity. There are a few members confused with your explanation.

Given a string of "| 12- 4" the code below will return 12 and 4 in the integer variables feet and inches respectively.

Code

Option Explicit
Sub Confused()
    Dim sStr As String
    Dim vStr As Variant
    Dim lLRow As Long
    Dim iLoop As Integer
    Dim iFeet As Integer, iInches As Integer


    lLRow = Range("A" & Rows.Count).End(xlUp).Row + 1


    For iLoop = lLRow To 2 Step -1
        sStr = Cells(iLoop, "E").Value
        If InStr(1, sStr, "-") > 0 Then
            vStr = Split(sStr, "-")
            If InStr(1, sStr, "|") > 0 Then vStr(0) = Replace(vStr(0), "|", "", 1)
            iFeet = Val(vStr(0))   'Here is your result feet
            iInches = Val(vStr(1))    'Here is your reuslt inches
        End If
    Next iLoop


End Sub

Display More

p45cal · Oct 10th 2007

Re: Macro To Delete The First Two Characters Of A Cell

Quote from StephenR

edelauna: all very confusing. Which columns are you using? You're looking for the last row in column B (LR), and then you're looking at column F (aiStr = Split(Cells(lRow, 6), " ")). If that's the relevant column you need to amend p45cal's code.

Confusing indeed! The words 'goalposts' and 'moving' spring to mind. I spend time answering one line queries which edelauna should be able to answer for himself and spend more time trying to weedle out the specifics from him. The result? Not a reply, no answers to the questions asked of him, [COLOR="Wheat"][SIZE="1"](no 'thanks' either)[/SIZE][/COLOR] but an amendment of his previous post where he deletes half of it and then just tacks a block of code on the end and asks 'any advice?'.
The important line of which is now

Code

aiStr = Split(Cells(lRow, 6), " ")

Where's the relevance to the title of this thread?
Why are we now delimiting with a space instead of a '-'?
What on earth is in Cells(lrow,6)? Unfortunately I don't have telepathic abilities.
Why the transfer of the data from one array to another.. and then some?
What went wrong with previous suggested solutions?

Edelauna, if you can't be bothered to spend time describing your problem lucidly, just tell me why others should bother spending time deciphering/guessing/mindreading/answering it?

All the above questions are rhetorical, since I'm bowing out of this thread - no I'm not, I'm running away as fast as I can!
p45cal

jindon · Oct 10th 2007

Re: Macro To Delete The First Two Characters Of A Cell

Quote from edelauna

Hello, I am trying to write a macro that will go through each cell in a column with the following format "| 12- 4" or "| 60-11" and will remove the first to character "| ".

There numbers are lengths, the first being feet the second inches. I would like to achieve in a separate column the numerical length (with decimals) multiplied by 1%. My code is as follows I just need help on the conversion.

UDF
Select any horizontal 2 consecutive cells and enter
=Edelauna(E1)
Confirm with Ctrl + Shift + Enter(Array entry)
Hope this works

Code

Function Edelauna(txt As String) As Variant '<- fixed
With CreateObject("VBScript.RegExp")
    .Pattern = ".*(\d+(\.\d+)?)-\s?(\d+(\.\d+)?)"
    Edelauna = Split(.replace(txt, "$1 $3"))  '<- fixed
End With
End Function

Code:edited Thans to Reafidy

**Reafidy** · Oct 10th 2007

Re: Macro To Delete The First Two Characters Of A Cell

Jindon,

Typo:

Code

Function Edelauna(txt As String) As [B]Variant[/B]
 'and
 Edelauna = Split(.[B]replace[/B](txt, "$1 $3"))

Dave Hawley · Oct 10th 2007

Re: Macro To Delete The First Two Characters Of A Cell

I think the question as orginally asked and the Thread Title has been answered.

Delete First X Characters In Cells

Participate now!

Share