Seperate a info in a cell with formula or script

[Crispy85]

Hi Everyone,

I've been looking at this for quite a time but i don't see any easy way to solve it.

I also added a testbook.
Basicly what i want is in cell C7, the same information as in B7, but without letters, if it contains any.

Can someone help me fix this?


Thanks!!

[hemesh]

Hello Crispy try this in C7
=LEFT(B7,4)&"-"&MID(B7,FIND("-",B7)+1,4)


this will work till you data setup is as in your sample file. If it can change please let me know

[Crispy85]

Hi Hemesh,

Thanks! Didn't really now about the FIND function.
It does solve my problem in the example, however, the format of the information givin in B7 isn't always fixed.

It can differ from 1-2, 23-1333, 1444a-11150B, 15950B-1434B. Do you know what i mean?
Do you have any solution for that?

Regards

[Crispy85]

Hi,

I think my first example file wasn't complete enough and i updated it.
As you can see, it not only contains letters but also versions like "-2" after a "link".

All of this should be removed. So for example 9095B-11071Z-2 should become 9095-11071

I added a new xlsx file.

Thanks in advance!!!

[john55]

hi,
I used it in the past, see if it helps you

Please Login or Register  to view this content.

[hemesh]

Hello Crispy copy and paste this in B7 .
=IF(ISNUMBER(LEFT(B7,FIND("-",B7)-1)+0),LEFT(B7,FIND("-",B7)-1),LEFT(B7,FIND("-",B7)-2))&"-"&IF(ISNUMBER(FIND("-",B7,FIND("-",B7)+1)),IF(AND(CODE(RIGHT(TRIM(MID(B7,FIND("-",B7)+1,FIND("-",B7,FIND("-",B7)+1)-FIND("-",B7)-1)),1))>=65,CODE(RIGHT(TRIM(MID(B7,FIND("-",B7)+1,FIND("-",B7,FIND("-",B7)+1)-FIND("-",B7)-1)),1))<=122),MID(TRIM(MID(B7,FIND("-",B7)+1,FIND("-",B7,FIND("-",B7)+1)-FIND("-",B7)-1)),1,LEN(TRIM(MID(B7,FIND("-",B7)+1,FIND("-",B7,FIND("-",B7)+1)-FIND("-",B7)-1)))-1),MID(TRIM(MID(B7,FIND("-",B7)+1,FIND("-",B7,FIND("-",B7)+1)-FIND("-",B7)-1)),1,LEN(TRIM(MID(B7,FIND("-",B7)+1,FIND("-",B7,FIND("-",B7)+1)-FIND("-",B7)-1))))),IF(AND(CODE(RIGHT(B7,1))>=65,AND(CODE(RIGHT(B7,1))<=122)),MID(TRIM(MID(B7,FIND("-",B7)+1,30)),1,LEN(TRIM(MID(B7,FIND("-",B7)+1,30)))-1),TRIM(MID(B7,FIND("-",B7)+1,30))))

[hemesh]

Find attached file

if you are satisfied with the ans click " * " add rep icon in the bottom left corner of my post
To Mark Thread as solved go to thread tools and mark as solved

[Crispy85]

Hi Hemesh,

****** that's what i call a formula.... Damn, thanks! and it does work! Now i can spend an hour trying to figure this formula out to check what it does exactly, lol! Thanks again!


Hi John55,

Thanks for your macro. It almost works. When you start it you can see for example in cell C12, it makes 9095-11071-2, while it should be 9095-11071.

[hemesh]

Thanks for the rep crispy ! Please mark thread as solved go to thread tools mark as solved

[martindwilson]

this somewhat shorter array formula in c7 should do it

Please Login or Register  to view this content.

[Crispy85]

Hi martindwilson,

It results in #N/A.

Do i miss something?

[martindwilson]

ARRAY entered with ctrl+shift+enter see link in my signature