66

I have a set of data that shown below on excel.

R/V(208,0,32)   YR/V(255,156,0)         Y/V(255,217,0)
R/S(184,28,16)  YR/S(216,128,0)         Y/S(209,171,0)
R/B(255,88,80)  YR/B(255,168,40)    Y/B(255,216,40)

And I want to separate the data in each cell look like this.

R/V 208 0 32
R/S 184 28 16
R/B 255 88 80

what is the function in excel that I can use for this case. Thank you in advance.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Celops
  • 661
  • 1
  • 5
  • 3
  • Data->Text to Columns from the menu bar may suit. – Fionnuala Apr 28 '10 at 08:54
  • I find a better method, for this case I obtein the firts value with +find function or +hallar function then I obtein the last value with the same function and for last with the function +extraer or mid obtein the value between the first value and last value, good with the differents between two values. – camilo lopez May 05 '17 at 21:40

6 Answers6

79

kennytm doesn't provide an example so here's how you do substrings:

=MID(text, start_num, char_num)

Let's say cell A1 is Hello.

=MID(A1, 2, 3)

Would return

ell

Because it says to start at character 2, e, and to return 3 characters.

pevik
  • 4,523
  • 3
  • 33
  • 44
Charles Clayton
  • 17,005
  • 11
  • 87
  • 120
  • I've updated the URLs in KennyTm's reply. Thanks for the heads-up on those broken links! –  May 15 '15 at 21:32
47

In Excel, the substring function is called MID function, and indexOf is called FIND for case-sensitive location and SEARCH function for non-case-sensitive location. For the first portion of your text parsing the LEFT function may also be useful.

See all the text functions here: Text Functions (reference).

Full worksheet function reference lists available at:

    Excel functions (by category)
    Excel functions (alphabetical)

Christopher Moore
  • 3,071
  • 4
  • 30
  • 46
kennytm
  • 510,854
  • 105
  • 1,084
  • 1,005
  • 4
    he could do this all with 1 function if he uses "substitute". No need to do complex string manipulation with search, mid, left, etc... also this isn't really solving the problem, it's just teaching about a couple string functions, and linking him to the MS pages... – John Smith May 15 '15 at 21:44
  • Thanks. Really misleading to name a `substring` function as `mid`. – Zsolti Oct 15 '18 at 09:18
18

Another way you can do this is by using the substitute function. Substitute "(", ")" and "," with spaces. e.g.

 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "(", " "), ")", " "), ",", " ")
andyb
  • 770
  • 5
  • 11
1

I believe we can start from basic to achieve desired result.

For example, I had a situation to extract data after "/". The given excel field had a value of 2rko6xyda14gdl7/VEERABABU%20MATCHA%20IN131621.jpg . I simply wanted to extract the text from "I5" cell after slash symbol. So firstly I want to find where "/" symbol is (FIND("/",I5). This gives me the position of "/". Then I should know the length of text, which i can get by LEN(I5).so total length minus the position of "/" . which is LEN(I5)-(FIND("/",I5)) . This will first find the "/" position and then get me the total text that needs to be extracted. The RIGHT function is RIGHT(I5,12) will simply extract all the values of last 12 digits starting from right most character. So I will replace the above function "LEN(I5)-(FIND("/",I5))" for 12 number in the RIGHT function to get me dynamically the number of characters I need to extract in any given cell and my solution is presented as given below

The approach was

=RIGHT(I5,LEN(I5)-(FIND("/",I5))) will give me out as VEERABABU%20MATCHA%20IN131621.jpg . I think I am clear.

bhaskar mudam
  • 131
  • 1
  • 4
0

Update on 11/30/2022

With new excel functions, you can use the following in cell C1 for the input in A1:

=TEXTJOIN(" ",,TEXTSPLIT(A1,{"(",",",")"}))

Here is the output: sample output file

David Leal
  • 6,373
  • 4
  • 29
  • 56
-4

What about using Replace all? Just replace All on bracket to space. And comma to space. And I think you can achieve it.

Toffee Tanaka
  • 1
  • 1