Copying a number from a string

**Excel-VBA** · 07-24-2019, 08:12 AM

Hi all,

I am trying (in VBA) to copy a value from a string before a specific word (Share), however there are some difficulties.

1. The string can vary somewhat, for example 2 scenario's:

John(10)(File A, 12341234 Share)
Frank B Share (99)(Document, John, 312 Share, Saved)

2. The value is not always the same digits. In the above cases, 12341234 and 312, but any length (up to .. say 20 digits) is possible

3. The word Share might actually appear in the front of the string (although unlikely), both separately or as part of another word / name (e.g. Frank B Share / Frank B Shared)

4. Most unlikely, but not inevitable, the word Share might not appear at all. In this case, it should skip this command all together and the sequential commands (I have piece of code included "sht.range("A1").value = LFoundValue"). In this case I will manually insert a value in cell A1 (after the macro has run). I would need to include a textbox at the end of my complete code though, to inform that cell A1 has been kept empty and manual work needs to be done. Please note that the next time running this macro, it should check if there is already a number in sht.range("A1"). If there is, nothing needs to be done (because I manually added the number).

Now I thought of finding the position of the comma "," and find the position of the spaces after the comma, but there might actually be more than 1. The same goes for the word "Share". This makes it more complicated.

Hopefully one of you has experience with a similar challenge.

**jindon** · 07-24-2019, 08:31 AM

Can you just upload a small example file with possible variation of data and the result that you want?

So that we can work on it.

**Kaper** · 07-24-2019, 08:43 AM

Probably the most appropriate approach would be by using regular expressions, but let me show more basic way - just with searching the string.

Try such function:

Please Login or Register  to view this content.

To test how it works (application to your code (not shown) is on you :-))
Write some sample texts in K1:K6, lets say

John(10)(File A, 12341234 Share)*
Frank B Share (99)(Document, John, 312 Share, Saved)
Peter
Share
asia Share
1234 Share mare lokus

and run such macro:

Please Login or Register  to view this content.

**Excel-VBA** · 07-24-2019, 10:04 AM

Hi Kaper,

Thank you for the quick reply!

This might sound strange, but I never learned to work with functions. I only write code in lengthy sub(), thus I wonder if it is possible to incorporate it in a sub (because I am not yet familiar with functions).

I just now realise my explanation was not detailed enough, please see below my old code. This relied on having exactly 2 commas in the string, which I found was not reliable, as other files had 1 or 3 (or more) commas.

Please Login or Register  to view this content.

Additionally, this does not incorporate my other scenario's as in my topic start post.

Hope you can help with a sub() version.

Sidenote, do you believe I should get familiar with functions?

**AliGW** · 07-24-2019, 10:09 AM

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

**Excel-VBA** · 07-24-2019, 10:17 AM

Originally Posted by jindon

Can you just upload a small example file with possible variation of data and the result that you want?

So that we can work on it.

Hi jindon,

Sure thing. Please see attached file.

Side note:

1. The string is always at another tab in position A1 (In my real model sht3.range("A1")
2. The value I would like to copy from the string is always copied to the same cell (in my real model sht.range("F33") (thus a different sheet than the sht3)
3. The possible other lines I provide in the attached sheet, could and would thus appear in A1, but now are in A8-10 just to provide you with various string options

Thanks

**Kaper** · 07-24-2019, 10:29 AM

edited - removed answer until moderator's request is fulfilled

**jindon** · 07-24-2019, 10:35 AM

For the data in your attachment.

Please Login or Register  to view this content.

**Excel-VBA** · 07-24-2019, 10:39 AM

Originally Posted by jindon

For the data in your attachment.

Please Login or Register  to view this content.

Hi jindon,

That looks very impressive, but very hard to read (for me).

Would it be rude to ask for a bit of explanation as to how to read it? That way I can learn how to work with the code.

If it is too difficult, nevermind I will try playing around with it.

**Excel-VBA** · 07-24-2019, 10:49 AM

Originally Posted by Kaper

edited - removed answer until moderator's request is fulfilled

Apologies, I have changed my reply to include the code brackets.

**jindon** · 07-24-2019, 10:49 AM

Used Regular Expressions, so google with the key words like VBA Regexp meta character. etc for the details.

It finds the pattern matching.

Please Login or Register  to view this content.

means
( open bracket
some string other than a comma and followed by a comma appears none or many and (block of numbers) followed by none or many string followed by Share and none or many string other than a closing bracket followed by a closing bracket.

When it finds the pattern to match within a string, you can retrieve the target part using submatches for backreference.

Only a brief explanation so, find the web that explains in detail.

**Excel-VBA** · 07-25-2019, 04:57 AM

Originally Posted by Kaper

edited - removed answer until moderator's request is fulfilled

Hi Kaper,

Would you be able - now that I fulfilled the moderator's request - to repost your solution?

I would like to use it, as to learn from it, if you will.

That would be much appreciated.

Originally Posted by jindon

Used Regular Expressions, so google with the key words like VBA Regexp meta character. etc for the details.

It finds the pattern matching.

Please Login or Register  to view this content.

means
( open bracket
some string other than a comma and followed by a comma appears none or many and (block of numbers) followed by none or many string followed by Share and none or many string other than a closing bracket followed by a closing bracket.

When it finds the pattern to match within a string, you can retrieve the target part using submatches for backreference.

Only a brief explanation so, find the web that explains in detail.

Thank you jindon!

I will google for VBA Regexp and hopefully be able to use the coding myself.

Much appreciated

**Kaper** · 07-25-2019, 08:33 AM

Sure,

But as I wrote in post #3 I also think that regular expressions could be the best way to do it. Anyway, the text deleted as per moderator request (now fulfilled):

Let's start with sidenote: yes

As for other issues: Having nor code neither workbook the answers would be very general. the same is with thje one I already gave: You said "to copy a value from a string before a specific word". But is this string in a cell? or may be already in a variable? or in some dataset?

Going back to sidequestion - having a function allows you treat all above cases the same way: as with buitlt-in functions.

You can use:

Please Login or Register  to view this content.

but also

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

etc.

You use InStr function the same way.

As for not answered parts: note that I focused on first 3 and some if 4th point - up to "the word Share might not appear at all. " then you can do whatever you want with the result of function - for instance check if it is a number.

And finally - yes, you can of course put all the code inside main procedure (sub).

It could look like (just guessing, as you published no sample file. But now, before publishing, try the codes from this and previous post):

Please Login or Register  to view this content.

**jindon** · 07-25-2019, 09:02 AM

Non Regular Expression solution.

Please Login or Register  to view this content.

**Excel-VBA** · 07-29-2019, 05:47 AM

Kaper, jindon,

I hope this is allowed (not considered spam), but I would like to thank you both and I have given both your posts reputation.

For ease of use and readability I have incorporated Kaper's solution, which is working great!

As for jindon, I have read this weekend about Regular Expression, which seems difficult (or just new to me) to write and read, but I will practice using it.

Much appreciated!

**Kaper** · 07-29-2019, 06:48 AM

You are welcome, and thanks for reputation.

It would be lovely if you could select thread tools (above your first post in this thread) and mark the therad as SOLVED.

PS. No, this is not spam. Nobody (i hope) treats kindness as spam :-)

Copying a number from a string

LinkBack

Thread Tools

Rate This Thread

Display

Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Re: Copying a number from a string

Thread Information

Users Browsing this Thread

Similar Threads

Convert text string, to single number based on number in string.

Copying all rows whose string column matches an input string.

Generating random number string based on Alphanumeric String

VBA Find all instances of a specific string and copying the number of count in cell

[SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string

My worksheet won't convert a number string to a shorter number string

[SOLVED] copying part of a number string

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions