Excel help

lsg1r

lsg1r

lsg1r

Soldato
Joined
25 Jan 2007
Posts
4,761
Location
King's Lynn
I have a cell that has a combination of text within asterisks, text/email within $ signs and then an entry from a range of entries (nd, am, pm, ndam, ndpm, sat, ndsat, bw, ndbw). It's possible that none of these are in the cell or all of them.

I can get the cell the cell to read the range of entries and put the relevent entry into the cell and leave empty if it's not there using IF formula, the issue is that I want to ignore the content between the asterisks and the content between the cells and using IF doesn't seem to do this.

Is there a way that I can do this?

Examples below with result I'd like:
1 - *text here nd* $ndemail@email$ nd = nd
2 - $email$ *text* SAT = sat
3 - *text* = blank
4 - ndbw = ndbw

Thanks for any help :)
 
dlockers said:
Can't you just handle each separate thing in a separate column, and then concatenate?
Click to expand...
Sadly no, it's coming in from another file in one cell.... I'm basically trying to separate multiple entries in one cell into different cells.
 
dlockers said:
Does it have to be a formula? Could just go nuts with cell to columns (or w/e it is called).
Click to expand...
It doesn't have to be a formula but I can't say I've done the cell to columns before, well not knowingly lol. The cells aren't next to each other if that makes a difference
 
lsg1r said:
Sadly no, it's coming in from another file in one cell.... I'm basically trying to separate multiple entries in one cell into different cells.
Click to expand...

If you need to seperate these out into seperate cells based such a large variety of delimiters you will need a VBA script or if it's an online 365 excel you can use Python
 
Last edited:
Grim5 said:
If you need to seperate these out into seperate cells based such a large variety of delimiters you will need VBA or if it's an online 365 excel you can use Python
Click to expand...
Even when it's really only 3 sections?
The ** bit, The $$ bit both of which I basically want to ignore and then the answer based on the remaining bit

I can't say I'm good with python or vba if I'm being honest.
 
Last edited:
I don't have excel to hand right now but I think I would try doing something like:
- Find the first asterisk
- Find the last asterisk
- concatenate string from before and after each location.
- same thing with dollars.

Or Just delete all text before the location of the last asterisk / dollar in the string if they always come before the text you want.

Would need to make sure none of the text strings you want contain asterisks or $s though because that could mess things up and need more handling.

Sorry if it doesn't work lol, that's what I'd try first though.
 
Last edited:
GreatAuk said:
I don't have excel to hand right now but I think I would try doing something like:
- Find the first asterisk
- Find the last asterisk
- concatenate string from before and after each location.
- same thing with dollars.

Or Just delete all text before the location of the last asterisk / dollar in the string if they always come before the text you want.

Would need to make sure none of the text strings you want contain asterisks or $s though because that could mess things up and need more handling.

Sorry if it doesn't work lol, that's what I'd try first though.
Click to expand...
Honestly that was my kind of my thinking... find the ** and turn it into a blank, find the $$ and turn it into a blank so they're both ignored, neither should include a $ or *.

I did try using concatenate when trying bing but it never seemed to work. I'll give it another go tomorrow.
 
In the name manager add the following custom function:

Name: REPLACEBETWEENCHAR

Formula:
Code: 
=LAMBDA(
    Str,
    Char,
    ReplaceWith,
        LET(
            CharArray,  DROP(
                            REDUCE(
                                "",
                                SEQUENCE(LEN(Str), , 1, 1),
                                LAMBDA(Accumulator, Value,
                                    IF(EXACT(MID(Str, Value, 1), Char), HSTACK(Accumulator, Value), Accumulator)
                                    )
                            ),
                            ,
                            1
                        ),
                IF(
                    COUNT(CharArray) <= 1,
                        Str,
                        REPLACEBETWEENCHAR(
                            REPLACE(
                                Str,
                                INDEX(CharArray, 1),
                                INDEX(CharArray, 2) - INDEX(CharArray, 1) + 1,
                                ReplaceWith
                            ),
                            Char,
                            ReplaceWith
                        )
                )
            )
)

The custom formula syntax is:

Code: 
=REPLACEBETWEENCHAR(String,FindCharacter,ReplaceWith)

E.g. to replace text between two asterisks:

Code: 
=REPLACEBETWEENCHAR(A1,"*","")

or to replace text between asterisks and dollars:

Code: 
=TRIM(REPLACEBETWEENCHAR(REPLACEBETWEENCHAR(A1,"*",""),"$",""))
 
As above either check whether you can make text to columns work or find the nth occurrence of the characters you need to identify and write an if around that, or handle the results of the find in multiple working columns before reconsolidation at the end
 
/edit - thinking about it some more, my proposal needs further refinement. Will try to update tomorrow.
 
Last edited:
yeah basically the above - do Find last instance (see link below) on both * and $ - compare both results then trim the string to leave whatever is right of the highest - which mgiht be blank.

trumpexcel.com

Find Position of the Last Occurrence of a Character in Excel

In this tutorial, you will learn how to find the position of the last occurrence of a character in a String in Excel (step by step explanation)
trumpexcel.com trumpexcel.com

or... put it in a database and use SQL :)
 
Last edited:
Had to do some serious nesting, but this seems to work:
Code: 
=IF(AND(ISNUMBER(SEARCH("~*", A1)), ISNUMBER(SEARCH(" ", A1))=FALSE), "",
 IF(AND(ISNUMBER(SEARCH("$", A1)), ISNUMBER(SEARCH(" ", A1))=FALSE), "",
 IF(ISNUMBER(SEARCH(" ", A1)), TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", LEN(A1))), LEN(A1))),
 A1)))

Untitled.jpg
 
You must log in or register to reply here.
Back
Top Bottom