Extract characters from a cell after a certain symbol

[canadian86]

In column A, I have this:

VBAScript-V1.5 - Opt#2296-Newyork Road - 12-31-10.xls

In column B, I want to show:

2296

I don't want the date to appear in column B, just the numbers right after the # sign.

Any ideas?

 

[Michael M]

Try this in B1 and copy down

=MID(A1,FIND("#",A1,1)+1,4)

 

[canadian86]

That works... the more I learn, the less I seem to know.

Thanks.

 

[dcardno]

if you know that you will always be getting four numbers (or characters), you can use a formula like:

=MID(I8, FIND("#", I8) + 1, 4)

this just looks for the 'pound sign' character and then returns the next four characters as a text string.

If the numbers can be variable-length you have a slightly more difficult problem. In this case, if you know the ending delimiter you can still get the string you are looking for with:

=MID(I8, FIND("#", I8) + 1, FIND("-",I8,FIND("#", I8)) - FIND("#", I8) - 1)

this operates the same way as the first version - it looks for the "#" and then returns the next "X" characters. In the first version, though, we knew (or assumed) that we would always want four characters. In the second version, we assume that we want an unknown number of characters, up to the next "-" character. The last part of the function looks for the first "-" in the search string after the '#' (ie, it calculates "X").

 

[canadian86]

Very cool... thanks a lot Dean!

 

[mladenjevtic]

[FONT=&quot]Hello,[/FONT][FONT=&quot]
I see this is a bit old thread, but i read whole topic regarding the extraction after a certain symbol from a cell and Extract Only Numbers From Text String and can't find any luck with my problem...[/FONT][FONT=&quot]
[/FONT][FONT=&quot]
I have column that has data like this, example:

A1: 8770235867 FRAGRANCE 5501 $120.10 8770235867 SCENTED $130.45 8770243769 NUMBER 3 $150.45
A2: 8770235867 FRAGRANCE 5501 $100 8770235867 SCENTED $130.1
A3: 8770235867 SCENTED $130

What i need is to extract the amounts of items and total them.
Note that items can vary from 1 through infinite (but from the experience it won't pass 30).
What is always constant is that no matter how many items are in a cell, the order is the same: XXXXXXXXXX (10 digit item code) XXXXXXXXX (item description - consists of letters and numbers, spaces, dashes,etc.) $XXXX.XX (price; always has $ before the amount and the price of one item is never larger than $9999.99, but doesn't always have decimal, it could be round number)

So what i want to do is to total the prices from one cell. In this above mentioned case:

A1: 8770235867 FRAGRANCE 5501 $120.10 8770235867 SCENTED $130.45 8770243769 NUMBER 3 $150.45
B1: $401.00

A2: 8770235867 FRAGRANCE 5501 $100 8770235867 SCENTED $130.1
B2: $230.10

A3: 8770235867 SCENTED $130
B3: $130

I found this one useful

[/FONT]

[FONT=&quot]=MID(I8, FIND("#", I8) + 1, 4)[/FONT][FONT=&quot]

, but it only extracts first items price.[/FONT][FONT=&quot]
This one is only giving me the error

[/FONT]

[FONT=&quot]=MID(I8, FIND("#", I8) + 1, FIND("-",I8,FIND("#", I8)) - FIND("#", I8) - 1)[/FONT][FONT=&quot]

.[/FONT][FONT=&quot]

Can you help me out please?[/FONT]

 

[mladenjevtic]

Hello,
I see this is a bit old thread, but i read whole topic regarding the extraction after a certain symbol from a cell and Extract Only Numbers From Text String and can't find any luck with my problem...

I have column that has data like this, example:

A1: 8770235867 FRAGRANCE 5501 $120.10 8770235867 SCENTED $130.45 8770243769 NUMBER 3 $150.45
A2: 8770235867 FRAGRANCE 5501 $100 8770235867 SCENTED $130.1
A3: 8770235867 SCENTED $130

What i need is to extract the amounts of items and total them.
Note that items can vary from 1 through infinite (but from the experience it won't pass 30).
What is always constant is that no matter how many items are in a cell, the order is the same: XXXXXXXXXX (10 digit item code) XXXXXXXXX (item description - consists of letters and numbers, spaces, dashes,etc.) $XXXX.XX (price; always has $ before the amount and the price of one item is never larger than $9999.99, but doesn't always have decimal, it could be round number)

So what i want to do is to total the prices from one cell. In this above mentioned case:

A1: 8770235867 FRAGRANCE 5501 $120.10 8770235867 SCENTED $130.45 8770243769 NUMBER 3 $150.45
B1: $401.00

A2: 8770235867 FRAGRANCE 5501 $100 8770235867 SCENTED $130.1
B2: $230.10

A3: 8770235867 SCENTED $130
B3: $130

I found this one useful , but it only extracts first items price.
This one is only giving me the error .

Can you help me out please?

I finally got an answer, you can check out the video right here: https://www.youtube.com/watch?v=Z6nDHdGda0c