Parsing numbers in strings/numbers concatenated together

[twckfa16]

Hello -

I need help in parsing out numbers that are contained in strings as well as numbers that need to be seperated
that are concatenated together. Any help would be appreciated. Attached is a sample file - Column 1 represents before the macro is run and cell B2 reperesents the desired result. I ahve no code to go along with this. My thought was to search for a space and then cast that into an integer.

Thank you.

[mooglebagel]

It appears in Cell B2, the numbers and the letters have not been separated and appear exactly the same as they do in Column A. Therefore, I'm not sure what you're trying to accomplish. Can you please clarify?

[JBeaucaire]

You can use these custom functions to accomplish this.

Please Login or Register  to view this content.

How to install the User Defined Functions:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The functions are installed and ready to use.


The function is used in a cell like a normal formula. IN your example workbook you would enter this formula in B1:


=CreateSortedArray(A1:A6, CHAR(10), 4)

Explanation:
=CreateSortedArray(MyRange As Range, Delim As String, SortLen As Long)

MyRange As Range - the range of cells to evaluate in the formula
Delim As String - the string that will separate the concatenated values. Char(10) is the text value of a linefeed
SortLen As Long - how many characters of each string to use for sorting, also how many numbers are used when a long number is split out into individual numbers.

[twckfa16]

Sure I can clarify.

Actually I was not too clear on some things.

First - The numbers and letters haven't been seperated. However, they are out of sequence and I wanted to get them in sequential order according to the numbers.
Second - One entry needs to be parsed and placed at the top due to the sequence of the numbers.

Thank you.

[JBeaucaire]

Post #3 provides an answer in UDF form that accomplishes the sorting results you demonstrated.

[twckfa16]

Thank you - I am working on it right now with your solution.

Looking at it again - I apologize I made some mistakes.

The results should be (in one cell):

1497
1498
1499 a = a

...........and so on.

Sorry about that.

[JBeaucaire]

And your test revealed it actually does that, right? I spotted that error in your example.

[JBeaucaire]

I spotted a tiny error in the code that caused the single numbers to occasionally duplicate. Post #3 above has been updated.

[twckfa16]

JBeaucaire - Truthfully, I don't really understand how that works, but it does.

Can I insert a carriage return so in one cell it looks like this:

1497
1498
1499 a = a
1500 b = b
1501 c = c
1502 d = d
1503 e = e

maybe its my version of excel but it placed the contents in one line with no spaces.

[twckfa16]

JBeaucaire - I put it inside of a macro and it executed perfectly! Not sure what I did .

Thank you very much!!!

[JBeaucaire]

WHen you use it in a cell, you need to format that cell so that it wraps text.