Extracting certain parts of a text in a cell

[nicdyb]

Hi!

I have a small problem. At the moment I do this manually, but the margin for error is too high for me to be doing this, as the numbers used based on the extraction is highly important.

Basically, I am looking up a "project name" where I have lets say;

Each sub category inside each project.

AB-B05789-11-11-11-11
AB-B05789-11-11-11-12
AB-B05789-11-11-11-13
etc...

each of these projects has a price column to the right of it.


After each unique project there is 1 line gap, where I currently write the beginning like this; AB-B05789 and I do a SUM(A1:A3) in the cell to right. This way I can keep control of each project, and the revenue connected to it.

Further on, I then extract from another report where I list all the projects connected to our firm. I controlcheck what we have registered as revenue, compared to what each project manager has done. This way we find out if anyone forgot to input their revenue on the specific project.

The way I do this currently is by using a VLOOKUP on exact matching values.

I would like a formula (if possible) that gives me auto function on the SUM and another one that lists "AB-B05789" meaning, it lists everything until the 2nd "-". The project can also have other names, such as B-4550, meaning it isnt always two letters in front. But it is always "-" that makes a new subcategory. "AB-B05789" is the highest point in the hierarchy.

Here is an example that probably makes it easier to understand.

Thanks.

-nicdyb

EDIT: H15 and H26 is supposed to be SUM functions that I manually put in. The formulas somehow disappeared.

[dilipandey]

Hi nicdyb,


Try using below formula:-

Please Login or Register  to view this content.

See attached:- Example(1).xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[nicdyb]

Thanks for the help, but it wasn't exactly what I was looking for, maybe I need to be more precise :P

The sheet I now upload; Column A to H is what I export from our billing system. Column L to P is something I make manually

I would like cell G15 to say; E-6446 and cell H15 to SUM(H3:H15)

Same with G26; ES-7566 and H26; SUM(H16:H26)

I need the G15 and G26 to say only the first part of the project name, or my values in Column L will not work.

[dilipandey]

Still I will go with my same formula


Apply below formula in first blank after row #2 in column g:-

Please Login or Register  to view this content.

and apply below formula in first blank after row #2 in column H:-

Please Login or Register  to view this content.

Now, Filter the Column F "Date" to have only blanks.. and copy paste the above formula in all of the below blanks till you have the data
Note:- You might need to select visible cells (alt + and then paste the formula towards down .... thanks.

see attached:-Example(1)(1).xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[nicdyb]

Thanks! :D I really need to learn how to do the trimming of cells. I still dont get it :P

This will help me a bunch

[dilipandey]

you are welcome.. Cheers


Regards,
DILIPandey

<click on below 'star' if this helps>

[nicdyb]

There is something fishy here. When I open your workbook, the formulas work fine, but when I then click the cell of formula; {=IF(LEN($A9)<9;SUM(ISNUMBER(SEARCH($A9;$A$3:$A8))*($B$3:$B8));"")}

the {} gets removed, and if I then press enter, the formula doesnt work anymore. What is wrong?

[dilipandey]

{} means that the formula is an array formula.. and you can get back these signs when you enter the formula using ctrl shift enter key combination

Regards,
DILIPandey

<click on below 'star' if this helps>