Solved Excel - copying part of a cell to a new cell

March 8, 2016 at 16:44:42
Specs: Macintosh
What excel formula would I use to copy just the number after "CallID=" which in Cell A1 is 172155416 over to Cell B1?

zttp://www.abcdefghijklm.com/webreports/audio.jsp?callID=172155416&mailboxID=280332&authentication=B4B15093

Thanks Totrio

message edited by Totrio


See More: Excel - copying part of a cell to a new cell

Report •

#1
March 8, 2016 at 18:32:05
✔ Best Answer
It depends.

The main function you need is the MID function which has this syntax:

MID(text, start_num, num_chars)

If every cell has the same numbers of characters before the string you want to extract (in this case 58) and the string is always the same number of characters (in this case 9), then a basic MID function can be used:

=MID(A1,58,9)

If some cells might have a different number of characters before the string you want to extract but the string is always the same number of characters (in this case 9), then a FIND function can be added to find the "equal sign" and use that as the start_num argument for the MID function:

=MID(A1,FIND("=",A1)+1,9)

If some cells might have a different number of characters before the string you want to extract and the string might have a varying numbers of characters also, then a couple of more FIND functions can be added to determine the number of characters between the "equal sign" and the "ampersand" and use that as num_chars argument for the MID function:

=MID(A1,FIND("=",A1)+1,FIND("&",A1)-FIND("=",A1)-1)

If there is nothing consistent for the formula to find, such as an equal sign before the string and an ampersand afterwards, then things get considerably more difficult and we'll need some more examples.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 8, 2016 at 19:47:30
As mentioned by DerbyDad03, there are few caveats with this
formula.

If your number is always the first set of numbers, going from left to right,
between the = symbol and the & symbol, try this formula with your string in cell A1

=MID(LEFT(A1,FIND("&",A1)-1),FIND("=",A1)+1,LEN(A1))

It is similar to DerbyDad03s.

MIKE

http://www.skeptic.com/


Report •

#3
March 8, 2016 at 22:13:59
Thank you very much DerbyDad03 and mmconaghy! All suggestions worked like a charm. I am going to use the simple syntax since all the data is consistent.

Report •

Related Solutions

#4
March 9, 2016 at 05:46:49
re: "I am going to use the simple syntax since all the data is consistent"

You say that now. ;-)

One of the more complicated options might not hurt just in case something changes with the input and you miss it. For example, if www. changes to www2. for load balancing at the host site, the "simple syntax" will return the wrong result.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 5, 2016 at 21:37:24
Hi could you help
In say cell A1 has this text in it
J DKNX CVIX K101 - M DKNX CVIC P191
How could I get cell C1 to show only M DKNX CVIC P191

Report •

#6
May 6, 2016 at 04:04:13
Please post your question in a new thread.

Think you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question