Character Count Until a Number is Reached

**HangMan** · 12-07-2012, 09:28 AM

I have a spreadsheet containing thousands of rows, each row shows a file path in the format /Root Folder Name/Nested Folder Name/File Name, however both the root folders and nested folders contain different numbers of charchters throughout. The one common element is that the nested folder always starts with a number, so for example:

/Jo Blogs and Sons/4. Annual Accounts/Fiscal Summary.xls

What I want to be able to do is to write a formula to remove the root folder name and just leave the nested folder and the file name, so I want to end up with

4. Annual Accounts/Fiscal Summary.xls

I therefore need a way of counting the number of characters before the number in each row, so I can then add this to a MID function, but I've no idea how to do this?

Can anyone suggest an easy way?

Many thanks

**Pepe Le Mokko** · 12-07-2012, 09:35 AM

Perhaps =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9}))

**alansidman** · 12-07-2012, 09:35 AM

Assume your data in A14, then

=RIGHT(A14,LEN(A14)-FIND("/",A14,2))

Alan

**HangMan** · 12-07-2012, 09:47 AM

Hi Pep Le Mokko and alansidman

Both solutions worked perfectly...

Many thanks for your help...

**Marcol** · 12-07-2012, 09:48 AM

And another ...

Formula:

Please Login or Register  to view this content.

[EDIT]

=RIGHT(A1,LEN(A1)-FIND("/",A1,2)) will only work in some cases, try it with this
"Test/Jo Blogs and Sons/4. Annual Accounts/Fiscal Summary.xls"