Can't separate values of a cell

[Catniption]

Hello there! I am new to the forum so please forgive me if I am repeating a question. I tried to find something similar, but could not find the answer I needed.

I have a cell with several values; however, Excel seems to only "see" the first item. I have tried to use Text to Columns with no success. I have tried copying and pasting the cell in a variety of formats with still no luck. I think a macro may work, but I am not skilled enough to know what to do. Here is an example of the cell data:

"PRT-BNS-SVR-01/p;2
PRT-BNS-EOC-01-5.5.04/-;1
PRT-BNS-SRD-01/t;4
PRT-BNS-SDD-03/f;1
PRT-BNS-SRD-03/g;1
PRT-BNS-SCI-01/bn;1
PRT-BNS-SAS-01/t;1
PRT-BNS-SDD-01/f;1
PRT-BNS-CIA-01/p;2
PRT-BNS-SVCP-01/r;2"


In this case, the first item is PRT-BNS-SVR-01/p;2. I need separations between the / the ; and between each item. I am not sure where the quotes come from, as this only happens when I copy and paste to text. I can not see them in Excel. When I use Text to Columns, the first material will separate, but it's like the others so not exist.

Thank you so very much for your assistance!

[Moo the Dog]

What is your expected result? For instance, for your first line, (PRT-BNS-SVR-01/p;2), is the expected result this:
PRT BNS SVR 01 p 2

This will do that (assuming the data is in cell A1):
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," "),";"," "),"/"," ")

- Moo

[Catniption]

Thanks, Moo. Not quite what I was after. Here is a file for what I have and what I need (I hope the upload worked)

Book3.xlsx

[Moo the Dog]

Assuming your 1st row of data is in cell A1,

For the first part in your provided workbook, (column A), try this:
=LEFT(A1,FIND("/",A1)-1)

For the second part, in column B, try this and fill down:
=TRIM(LEFT(MID(SUBSTITUTE(A1,"/",REPT(" ",100)),100,100),FIND(";",A1)-1))

- Moo

[Moo the Dog]

I just realized that your data is actually in one CELL, not one column. Don't know/have enough time right now for a VBA solution, but one method that may work is this (with your data in cell A2):

1. In B2 type: =SUBSTITUTE(A2,CHAR(10)," ")
2. Copy cell B2 and 'Paste Special: Values' into cell B3
3. Run a 'Text-to-Columns' on cell B3, using a SPACE as the delimiter.
4. After the T-t-C, select B3 to the end of the range to the right, click copy
5. Select cell B4 and click 'Paste Special: Transpose'

That will give you your individual items in rows. You can then use my previously mentioned formulas in cells C4 and D4 and down.

First part:
=LEFT(B4,FIND("/",B4)-1)

Second part:
=TRIM(LEFT(MID(SUBSTITUTE(B4,"/",REPT(" ",100)),100,100),FIND(";",B4)-1))

- Moo

[Catniption]

No luck, Moo. I attached a file what I get when I try to use T to C. It always loses everything after the first item.

Doc1.docx

[Moo the Dog]

Interesting, it worked for me. See attached file.

- Moo

[Catniption]

Thank you so much! I noticed in your workbook that you had '=SUBSTITUTE(A2,CHAR(13)," ") but in the actual cell, you had '=SUBSTITUTE(A2,CHAR(10)," "). I have no idea what the "13" or "10" done, but when I used 10, it worked! I'll be using this often with a variety of information. Could you please explain what this is? I'm sure I will have different characters every time.

You have saved my tail on this one! Thank you, Thank you, Thank you!!

[Moo the Dog]

Oops... I was working with two different characters, 13 and 10.... those are codes for actual letters, numbers, symbols, etc. For instance, if you type a capital 'W' in cell A1, then in another cell type =CODE(A1), it will return 87. A lowercase 'w' returns 119.

I changed my formula above to reflect CHAR(10) now.

CHAR(10) is Excel's code for a line-break. What the formula does is substitutes a space (" ") in place of the line breaks - that way you can do a text-to-columns based on a space delimiter.

VBA would handle this more easily, but I have a lot of stuff going on and can't test my VBA skills at this moment. =)

- Moo

[Catniption]

You are AWESOME!

[Moo the Dog]

Thanks. The feedback is greatly appreciated.

- Moo