Extract each 10 characters in a cell, and save into an array

[Apple1]

Hi,

I have many cells with contents like the below:
XX Jun2003 XX Mar2007 XX Jan2012

I want to extract XX Jun2003, XX Mar2007, XX Jan2012 and save each of them into an array.

The number of such XX mmmyyyy differ for each cell.

Thank you

[AliGW]

Do you mean that you want to extract them to separate cells?

[jindon]

XX Jun2003 XX Mar2007 XX Jan2012

XX fixed? or can be any character?

How do you want it?

[Apple1]

Hi

1) XX can be any letter in the alphabet
2) I don't want to extract them into separate cells. I need to test the date for each and remove those that are not within a date range

Thank you

[jindon]

2) .... I need to test the date for each and remove those that are not within a date range

What date range?

[jindon]

Assuming data in col.A
Don't understand about "date range" and how you use it, so this will just create array of 10 characters in each element.

Please Login or Register  to view this content.

[Apple1]

Hi

Thank you for yr reply. But can u explain your code? Eg what is "a"?

Can you just provide a code to extract all the "XX mmmyyyy" in cell A1, and save each of the XX mmmyyyy in an array?

Thank you

[jindon]

1)
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

2) You said

I want to extract XX Jun2003, XX Mar2007, XX Jan2012 and save each of them into an array.

Do you really understand what is an "Array" ?

[AliGW]

Can you just provide a code to extract all the "XX mmmyyyy" in cell A1, and save each of the XX mmmyyyy in an array?

I think you need to provide a sample workbook with the data as it is now and what you are hoping to achieve mocked up manually. See the instructions at the top of the page.

[Apple1]

Hi

An array is a matrix of dimension x*y. I want to save
MyArray(0) = XX Jun2003
MyArray(1) = XX Mar2007
MyArray(2) = XX Jan2012

Thank you

[Rick Rothstein]

Does this macro do what you want? The last line creates a one-dimensional, zero-based array wherein each array element contains values that contain two letters, a space, the three-letter month name and a four-digit year.

Please Login or Register  to view this content.

Note: Data is assumed to be in Column A starting on Row 1.

[jindon]

That what I did already.

Please Login or Register  to view this content.

[Apple1]

Hi

Yes this is what I want. Can you explain your code?

What does this do?

Please Login or Register  to view this content.

Thank you

[Apple1]

Hi

Can you explain what is a = .Value?

Why do we need to place the $ in LEFT and TRIM?

Thank you

[jindon]

OK, I think I misread your question.

Please Login or Register  to view this content.

[Rick Rothstein]

What does this do?

Please Login or Register  to view this content.

When assigned to a Variant variable, Application.Transpose takes a vertical range of cells and places them into a one-dimensional array. The Join function then makes a single text string out of the elements of the array using a single space character to delimit them. The next step is to loop the text string every 11 characters starting at position 11. The Mid(S,X)="|" use the Mid in its statement form which allows you to replace characters in a text string at specified positions (in this case, the space character between the values you want). Once those "|" characters have been inserted, the text string is Split at the "|" characters to produce the array you wanted.

Note: In the future when more than one person has responded to you, you should mention the name of the person you are responding to instead of just saying "Hi" so we know for sure who should respond to your follow up question.

[jindon]

Can you explain what is a = .Value?

Do a step debug for yourself.
While you are in VBE;
1) [View]- [Local Window]
2) click on somehwere on the code and hit F8.
3) As you hit F8, the code will execute one line and you will see all the variables in Local Window.
4) You can expand the varialbe with + sign, so you can see.

Why do we need to place the $ in LEFT and TRIM?

Open [Object Browser] and enter Left in search box and you will see in the box below.