Finding Character Number of the Second "/" in a cell

[BigNate]

Hello Everyone,

Let's say I have column A with cells that have values such as the following:
/USA-UA113256725/AAA542/25

The number of characters will not be the same for all cells in column A
My end goal is to write a formula in B to return "UA113256725" as the result. So I am thinking to myself that I need to do a MID function, find the position of the second "/" (use this for LEN portion of formula) and start with the position of the "-". However, I do not know how to identify the position of the second "/". Can someone please help me?

Thanks in advance for your help.

 

[barry houdini]

You can get the position of the second "/" like this

=FIND("/",A1,FIND("/",A1)+1)

 

[mrshl9898]

To find the 2nd "/" use


=FIND("/",A2,FIND("/",A2)+1)

=MID(A2,FIND("-",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("-",A2)-1) will return UA113256725

 

[JoeMo]

Excel Workbook
	A	B
1	/USA-UA113256725/AAA542/25	UA113256725
Sheet2 (3)

 

[Rick Rothstein]

You can get the position of the second "/" like this

=FIND("/",A1,FIND("/",A1)+1)

If the single posted example is representative of the data that can appear in cell A1, then this would do...

=FIND("/",A1,2)

and this to get the sought after value...

=MID(A1,FIND("/",A1,2)-11,11)

or, if the dash is universal...

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

Again, assuming that single example is truly representative of the data to be parsed.

 

[BigNate]

I appreciate everyone's input. Thank you very much. This has allowed me to look at this challenge in a new way.