Remove characters from string (String normalization?)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I am attempting to remove characters from a string in excel by utilizing a VBA macro.For example the string is "UOZV3A-WB1○1.8ml vbn958Xzlv2" and I need it to return "UOZV3A-WB1". It is pretty straight forward, the code I am using is:

For Each c In Range("D2:D69")

If InStr(c.Value, "?") > 0 Then

    c.Value = Left(c.Value, InStr(c.Value, "?") - 1)

End If



Next c

The issue I am running into is a single character in the string ("o") is unrecognized by the macro. The string is entered into the cell by scanning a QR code. I suspect that "o" is a sort of placeholder that is recognized/interpreted as a "o" in excel but interpreted differently in VBA. If I try to just copy and paste the character into VBA I get a "?".

Is there a way to manipulate or interpret that character in VBA? Some of the other posts I read seemed to indicate that the string could be normalized but the coding was over my head.

Thanks!

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

asked Nov 16 '18 at 13:53

Thomas Bever

The character is the 11th in your string. If that string is in A1 then the formula =CODE(MID($A$1,11,1)) will tell you the Ascii code for the character. In VBA use Asc(Mid(Range("A1"), 11, 1)). Once you know the Ascii number you can use If Instr(c.Value, CHR(63))>0 Then (replacing 63 with the correct number).

– Darren Bartrup-Cook
Nov 16 '18 at 14:18

1

Thank you very much. I hadn't thought about the characters in terms of Ascii codes. I was able to get it to work that way.

– Thomas Bever
Nov 16 '18 at 16:38

add a comment |

For Each c In Range("D2:D69")

If InStr(c.Value, "?") > 0 Then

    c.Value = Left(c.Value, InStr(c.Value, "?") - 1)

End If



Next c

Is there a way to manipulate or interpret that character in VBA? Some of the other posts I read seemed to indicate that the string could be normalized but the coding was over my head.

Thanks!

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

asked Nov 16 '18 at 13:53

Thomas Bever

The character is the 11th in your string. If that string is in A1 then the formula =CODE(MID($A$1,11,1)) will tell you the Ascii code for the character. In VBA use Asc(Mid(Range("A1"), 11, 1)). Once you know the Ascii number you can use If Instr(c.Value, CHR(63))>0 Then (replacing 63 with the correct number).

– Darren Bartrup-Cook
Nov 16 '18 at 14:18

1

Thank you very much. I hadn't thought about the characters in terms of Ascii codes. I was able to get it to work that way.

– Thomas Bever
Nov 16 '18 at 16:38

add a comment |

For Each c In Range("D2:D69")

If InStr(c.Value, "?") > 0 Then

    c.Value = Left(c.Value, InStr(c.Value, "?") - 1)

End If



Next c

Is there a way to manipulate or interpret that character in VBA? Some of the other posts I read seemed to indicate that the string could be normalized but the coding was over my head.

Thanks!

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

asked Nov 16 '18 at 13:53

Thomas Bever

For Each c In Range("D2:D69")

If InStr(c.Value, "?") > 0 Then

    c.Value = Left(c.Value, InStr(c.Value, "?") - 1)

End If



Next c

Is there a way to manipulate or interpret that character in VBA? Some of the other posts I read seemed to indicate that the string could be normalized but the coding was over my head.

Thanks!

excel vba string excel-vba normalization

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

asked Nov 16 '18 at 13:53

Thomas Bever

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

asked Nov 16 '18 at 13:53

Thomas Bever

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

edited Nov 16 '18 at 14:46

Pᴇʜ

25.2k63052

asked Nov 16 '18 at 13:53

Thomas Bever

asked Nov 16 '18 at 13:53

Thomas Bever

asked Nov 16 '18 at 13:53

Thomas Bever

The character is the 11th in your string. If that string is in A1 then the formula =CODE(MID($A$1,11,1)) will tell you the Ascii code for the character. In VBA use Asc(Mid(Range("A1"), 11, 1)). Once you know the Ascii number you can use If Instr(c.Value, CHR(63))>0 Then (replacing 63 with the correct number).

– Darren Bartrup-Cook
Nov 16 '18 at 14:18

1

Thank you very much. I hadn't thought about the characters in terms of Ascii codes. I was able to get it to work that way.

– Thomas Bever
Nov 16 '18 at 16:38

add a comment |

The character is the 11th in your string. If that string is in A1 then the formula =CODE(MID($A$1,11,1)) will tell you the Ascii code for the character. In VBA use Asc(Mid(Range("A1"), 11, 1)). Once you know the Ascii number you can use If Instr(c.Value, CHR(63))>0 Then (replacing 63 with the correct number).

– Darren Bartrup-Cook
Nov 16 '18 at 14:18

1

Thank you very much. I hadn't thought about the characters in terms of Ascii codes. I was able to get it to work that way.

– Thomas Bever
Nov 16 '18 at 16:38

The character is the 11th in your string. If that string is in A1 then the formula =CODE(MID($A$1,11,1)) will tell you the Ascii code for the character. In VBA use Asc(Mid(Range("A1"), 11, 1)). Once you know the Ascii number you can use If Instr(c.Value, CHR(63))>0 Then (replacing 63 with the correct number).

– Darren Bartrup-Cook
Nov 16 '18 at 14:18

Thank you very much. I hadn't thought about the characters in terms of Ascii codes. I was able to get it to work that way.

– Thomas Bever
Nov 16 '18 at 16:38

add a comment |

1 Answer
1

active

oldest

votes

You need to understand what character you are parsing on:

Sub junkkiller()

    For Each c In Range("D2:D69")

        If InStr(c.Value, ChrW(9675)) > 0 Then

            c.Value = Left(c.Value, InStr(c.Value, ChrW(9675)) - 1)

        End If

    Next c

End Sub

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

1

Awesome! Thank you for the clarification. I hadn't thought about determining the code for that character.

– Thomas Bever
Nov 16 '18 at 16:36

@ThomasBever Glad to help. Thanks for the feedback!

– Gary's Student
Nov 16 '18 at 16:47

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53339229%2fremove-characters-from-string-string-normalization%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You need to understand what character you are parsing on:

Sub junkkiller()

    For Each c In Range("D2:D69")

        If InStr(c.Value, ChrW(9675)) > 0 Then

            c.Value = Left(c.Value, InStr(c.Value, ChrW(9675)) - 1)

        End If

    Next c

End Sub

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

1

Awesome! Thank you for the clarification. I hadn't thought about determining the code for that character.

– Thomas Bever
Nov 16 '18 at 16:36

@ThomasBever Glad to help. Thanks for the feedback!

– Gary's Student
Nov 16 '18 at 16:47

add a comment |

You need to understand what character you are parsing on:

Sub junkkiller()

    For Each c In Range("D2:D69")

        If InStr(c.Value, ChrW(9675)) > 0 Then

            c.Value = Left(c.Value, InStr(c.Value, ChrW(9675)) - 1)

        End If

    Next c

End Sub

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

1

Awesome! Thank you for the clarification. I hadn't thought about determining the code for that character.

– Thomas Bever
Nov 16 '18 at 16:36

@ThomasBever Glad to help. Thanks for the feedback!

– Gary's Student
Nov 16 '18 at 16:47

add a comment |

You need to understand what character you are parsing on:

Sub junkkiller()

    For Each c In Range("D2:D69")

        If InStr(c.Value, ChrW(9675)) > 0 Then

            c.Value = Left(c.Value, InStr(c.Value, ChrW(9675)) - 1)

        End If

    Next c

End Sub

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

You need to understand what character you are parsing on:

Sub junkkiller()

    For Each c In Range("D2:D69")

        If InStr(c.Value, ChrW(9675)) > 0 Then

            c.Value = Left(c.Value, InStr(c.Value, ChrW(9675)) - 1)

        End If

    Next c

End Sub

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

answered Nov 16 '18 at 14:21

Gary's Student

75.2k94164

1

Awesome! Thank you for the clarification. I hadn't thought about determining the code for that character.

– Thomas Bever
Nov 16 '18 at 16:36

@ThomasBever Glad to help. Thanks for the feedback!

– Gary's Student
Nov 16 '18 at 16:47

add a comment |

1

Awesome! Thank you for the clarification. I hadn't thought about determining the code for that character.

– Thomas Bever
Nov 16 '18 at 16:36

@ThomasBever Glad to help. Thanks for the feedback!

– Gary's Student
Nov 16 '18 at 16:47

Awesome! Thank you for the clarification. I hadn't thought about determining the code for that character.

– Thomas Bever
Nov 16 '18 at 16:36

@ThomasBever Glad to help. Thanks for the feedback!

– Gary's Student
Nov 16 '18 at 16:47

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky