Substring in excel

Question

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

User · Answer

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

User · Answer

kennytm s links are dead and he 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

User · Answer

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

User · Answer

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

User · Answer

Another way you can do this is by using the substitute function  Substitute          and     with spaces  e g     SUBSTITUTE SUBSTITUTE SUBSTITUTE A1

[excel] Substring in excel

Examples related to excel

Examples related to string

Examples related to excel-formula

Examples related to substring