# Need formula to parse data

#### djl0525

##### Well-known Member
Setup
Sheet1!H5:AA5 contains alphanumeric entries like X3, N2, E1, G4, H2, AL1, B1, etc. The alpha part of the entries is unique and can be one or two characters. The numeric part is always one digit (1-9).

Goal
I need to get the numbers that were entered in random order from Sheet1 to a specific order on Sheet2 (without the alphas).

Example 1
In Sheet2!H5, I need a formula that will look at Sheet1!\$H5:\$AA5 and if any cell in that range begins with the letter "G", return the number that comes after G, which is 4. If there is no G, return blank.

Example 2
In another cell, the formula will be edited to look for entries that begin with "AL", for example, and return the number after AL, which is 1. If there is no AL in the range, return blank.

Note: Each cell on Sheet2 will look for a different alpha on Sheet1 and return the value after the alpha.

Any assistance will be greatly appreciated!
-- DJ

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

##### Well-known Member
Sample data table:

Excel Workbook
HIJKLMNOPQRSTUVWXYZAA
1LZ0B6OQ1Z0H2R2EO9J4T4FH2QT4I3NJ4MY9AV6X8JJ4R2SZ0BG1
2K5HG1MG1SF0F0X8O9Y9RV6G1Q1GV6P0GO9K5OI3MV6A5E9Q1
3Q1A5OX8GB6ES3CM7SA5I3L6R2ZU5R2Z0K5I3JS3UT4M7B6N8
4MJ4T4X8Z0KY9T4LH2SN8B6C7A5W7RC7G1V6CC7A5R2TB6N8
5I3V6A5ZP0I3RJ4RX8W7BP0A5LK5ND8Y9G1MW7Q1Q1VJ4WB6ZB6
Sheet1

Sample formula:

Excel Workbook
AB
1KY9
Sheet1

Looks at the cell in A1, finds any value that matches in H1:AA5, and figures out the number on the right. I assume that all KY would end in the same number, so that there isn't a case where you'd have KY3 and KY6 in the same data set.

#### djl0525

##### Well-known Member
Wow. Thanks so much for your help! I don't know how you did it, but you did it! There's just one thing I failed to mention earlier. Some cells are blank in the range which results in the #VALUE! error.

Also, I made 2 changes to the formula:

1. The data is on Sheet1, but the formulas are on Sheet2.
2. The data range is H5:AA5, not H5:AA9.
The current array formula is:
=AVERAGE(IF(LEFT(Sheet1!\$H5:\$AA5,LEN(Sheet1!\$H5:\$AA5)-1)=H\$1,--(RIGHT(Sheet1!\$H5:\$AA5,1))))

Can you edit it to allow blanks in H5:AA5?

Thanks again!
--DJ

##### Well-known Member
Okay, Data Set:
Excel Workbook
HIJKLMNOPQRSTUVWXYZAA
1Z0U5CI3Q1WE9CW7FR2SY9R2A5GM7G1Z0IZ0
2B6UA5Q1VU5AK5PW7T4H2XQ1NA5QR2K5TD8
3K5V6V6P0XS3ZI3M7SO9T4L6WT4LM7X8I3
4LO9TZ0FW7VS3LT4ML6X8P0Y9E9VY9L6
5L6YY9D8I3QJ4Z0KZ0F0G1Y9
6R2YZ0ON8SB6Y9G1BB6L6W7NQ1
7U5Z0S3FC7E9I3EJ4MI3T4A5
8BG1LV6N8R2FN8DG1YD8B6I3K5IC7AE9
9OY9ZZ0EK5N8FL6SA5QC7UI3HG1BN8Q1VO9H2
Sheet1

Here's the solution:
Excel Workbook
AB
1CI3
Sheet2

#### faistone

##### New Member
Re: Need formula to make a list of data from a running real time data entries.

Hello MrExcel, i want you to help me now.
How we get an updated list data entries directly from a running and real time recording?
example:
Day 1st
Time

07.00 John 5 units
07.30 Smith 2 units
09.00 Alexa 3 units
10.15 John 2 units

Day 2nd

07.15 Smith 1 unit
07.20 John 10 units
09.00 Edward 6 units
10.15 John 7 units

ect....

Here I want to make a list moving data that record my customers directly and real time.
This is my goal.

07.15 Smith 1 units
07.20 John 10 units
09.00 Edward 6 units

10.15 John moves to 17 units automatically(data 07.20 - John removed) and continue to the day 2nd ect....

Thanks all..

Good luck -- DJ

#### djl0525

##### Well-known Member
Sal, it does not return blank if it does not find the letter(s) it's looking for. It returns #DIV/0!. Please advise.

=AVERAGE(IF(Sheet1!H5:AA5<>"",IF(LEFT(Sheet1!H5:AA5,LEN(Sheet1!H5:AA5)-1)=H\$1,--(RIGHT(Sheet1!H5:AA5,1)))))

--DJ

Last edited:

Excel Workbook
AB
1ZY
Sheet2

This works.

#### djl0525

##### Well-known Member
The error is gone, but the formula puts zeroes where there should be blanks. I'll live with it. I'll come up with a workaround or something.

Formula I'm using...
=IF(ISERROR(AVERAGE(IF(Sheet1!\$H3:\$AA3<>"",IF(LEFT(Sheet1!\$H3:\$AA3,LEN(Sheet1!\$H3:\$AA3)-1)=H\$1,--(RIGHT(Sheet1!\$H3:\$AA3,1)))))),"",AVERAGE(IF(Sheet1!\$H3:\$AA3<>"",IF(LEFT(Sheet1!\$H3:\$AA3,LEN(Sheet1!\$H3:\$AA3)-1)=H\$1,--(RIGHT(Sheet1!\$H3:\$AA3,1))))))

I really appreciate the time you spent on this. It will be a major time-saver for several people!!

Thanks again! -- DJ

#### djl0525

##### Well-known Member
Nevermind what I said about getting zeroes. I discovered some of the formulas weren't arrays as they should have been. I fixed that and now it's working perfectly, just as you wrote it!

Thanks! -- DJ

Replies
1
Views
97
Replies
5
Views
157
Replies
1
Views
203
Replies
3
Views
203
Replies
8
Views
155

1,195,942
Messages
6,012,428
Members
441,699
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back