# Delete First X Characters In Cells

• 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.

• 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)``
• Re: Macro To Delete The First Two Characters Of A Cell

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:

• 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

Any advice on to how to resolve this error?

• 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

• 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.

• 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.

• 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

• 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

• 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"))``````
• Re: Macro To Delete The First Two Characters Of A Cell

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!