Menu
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.
769
Although there is no such thing as Substring function in Excel, there exist three Text functions (LEFT, RIGHT, and MID) to extract a substring of a given length. Also, there are FIND and SEARCH functions to get a substring before or after a specific character.
If you want to extract part string between two same characters, you can do as this: Select a cell which you will place the result, type this formula =SUBSTITUTE(MID(SUBSTITUTE("/" & A3&REPT(" ",6),"/",REPT(",",255)),2*255,255),",",""), and press Enter key.
The SUBSTR( ) function returns characters from the string value starting at the character position specified by start. The number of characters returned is specified by length.
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.
106
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)
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
