more-arw search

Q&A Forum

Excel Question

Hello- Would you please let me know if there is a way by using excel function to pull: 1) invoices from a string of texts below? 2) Invoices related to the associated companies only? i.e. I would like to pull only invoice started with CO#3 and disregard the all others sales order from different cost centers. Co Account Jrl Posting Remarks 3 31820-000 JE 501907 Move deposit from 83-122017 to 3-162378 3 31820-000 JE 502220 Move deposit from 3-162395-3 to 42-158469 3 31820-000 JE 502515 Move deposit from 3-159646-1 to 15-157224 3 31820-000 JE 502599 Move deposit from 3-162474-2 to 20-176835 3 31820-000 JE 502718 Move deposit from 82-182059 to 3-162502 Thank you,

Answers

Anonymous
(Director of Finance) |

Where are the strings of text resident? In a text file - either tab delimited or csv?
If you can open the file with excel, you can use a text to column function, probably using fixed width fields to get the data broken into columns and then either sort or filter it to get what you want.

eden meng
Title: Senior Accountant
Company: ABC
(Senior Accountant, ABC) |

Thank you for your respond. but the text length is not consistent. For example, some invoices is 3-162378 ( which is 8 characters) and some could be 9 or 10 characters (82-182059) or (231-182160-4). That is why I could not use the text to column function to break them out.

Pragya Tandon
Title: Director of Finance
Company: ClearVoice, Inc.
(Director of Finance, ClearVoice, Inc.) |

You could use a combination of mid, len and if formulas to do this. Below is an example:
=IF(AND(LEN(MID(A26,41,9))=9,MID(A26,50,1)=" "),MID(A26,40,10),MID(A26,40,11))

The above formula would work for invoice numbers that are 10 or 11 characters. Keep adding more if statements to cover the other ones.

Column A Column B
(source) (result)
3 31820-000 JE 502599 Move deposit from 3-162474-2 to 20-176835 3-162474-2
3 31820-000 JE 502718 Move deposit from 82-182059 to 3-162502 82-182059

Hope this helps!

Len Green
Title: Performance Improvement Consultant and E..
Company: Haygarth Consulting LLC
LinkedIn Profile
(Performance Improvement Consultant and ERP Strategist, Haygarth Consulting LLC) |

Anon
What is it you are trying to achieve in the end?

Are you exporting data from your ERP system into Excel? Why can't you inquire into the ERP system instead?

3590 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.