Text Manipulation, extracting and changing

[TheHobbit81]

Hi All,

I need some help with an Excel problem that I have. I need to extract some data from one column, modify it and put it into another.

Scenario:
Sheet 1 (Raw Data), Cell U2 contains the string "MMA>Throwdown>T-Shirts>Male"
and in Sheet 2 (Result), Cell C4 I need it to end up as "MMA/Throwdown"

What I'm trying to achieve:
Take the data from one cell, extract the data before the first and second ">" symbol and change the first ">" symbol to a "/" and have the result be in another cell.

The Problem:
I know that there are ways to identify specific strings of text but I have no idea what they are or how to use them.

Help is greatly appreciated.

About attachment:
File: CSVI by HobStar.xlsx

[Trebor76]

Hi there,

Try this formula in cell C4 of the Result tab and then copied down as required:

=LEFT('Raw Data'!A2,SEARCH(">",'Raw Data'!A2)-1)&"/"&TRIM(MID(SUBSTITUTE('Raw Data'!A2,">",REPT(" ",100)),100,100))

Regards,

Robert

[TheHobbit81]

Thanks Robert,

It comes up with a "#VALUE!" error. Any thoughts?

Warm Regards,
Paul

[TheHobbit81]

Thanks Robert,

It comes up with a "#VALUE!" error. Any thoughts?

Warm Regards,
Paul

Never mind, I just needed to change "A2" to "U2" and it works!!!

Thanks heaps Robert, you just made my day. =D

[Trebor76]

you just made my day

Glad to hear it

Please don't forget to mark the thread as solved.