Replace charecter string with leading character
Closed
davidplowman
Posts
5
Registration date
Wednesday April 3, 2013
Status
Member
Last seen
July 8, 2013
-
May 1, 2013 at 06:41 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 4, 2013 at 10:46 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 4, 2013 at 10:46 AM
Related:
- Replace charecter string with leading character
- Pipe character - Guide
- Reverse text character - Guide
- How to change chrome dino character - Guide
- Special character letter - Guide
- Unexpected t string php - Guide
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 1, 2013 at 11:15 PM
May 1, 2013 at 11:15 PM
suppose the data as follows in A1,A2,a3
5400 3-179 Diesel
5400N 3-179 Diesel
5500 4-239 Diesel
now in B1 type this formula
=MID(A1,1,SEARCH(" ",A1))
and c;opy do;wn
In c1 the formula is
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))
and c;opy dowan.
is thsis what you want.
5400 3-179 Diesel
5400N 3-179 Diesel
5500 4-239 Diesel
now in B1 type this formula
=MID(A1,1,SEARCH(" ",A1))
and c;opy do;wn
In c1 the formula is
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))
and c;opy dowan.
is thsis what you want.
Kevin@Radstock
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
May 2, 2013 at 02:32 AM
May 2, 2013 at 02:32 AM
Assuming you data is in A1:A3.
In B1 and copy down:
=LEFT(A1,FIND(" ",A1)-1)
Then in C1 and copy down:
=TRIM(SUBSTITUTE(A1,B1,""))
Kevin
In B1 and copy down:
=LEFT(A1,FIND(" ",A1)-1)
Then in C1 and copy down:
=TRIM(SUBSTITUTE(A1,B1,""))
Kevin
davidplowman
Posts
5
Registration date
Wednesday April 3, 2013
Status
Member
Last seen
July 8, 2013
May 2, 2013 at 12:25 PM
May 2, 2013 at 12:25 PM
Hi All:
I greatly appreciate your taking the time out to answer my question.
I have already used formulas and the LEN command to parse out dates in this string, but I'm not sure if that will be the best solution for these moving forward.
Just to clarify, the list I gave was rather "idealized" since there will be literally hundreds or thousands of different items I need to break up, and they will all have a varying length, some will be one word, some will be two, etc.
Just to illustrate here's just a few rows I'm parsing out to separate columns:
Initial Text
250 (EFI) GM 3.0L - 183ci - 6cyl
225CXL 3.0L - 185.0ci - 225 H.P.
225CXL EFI 3.0L - 185.0ci - 225 H.P.
225CXL Seapro 3.0L - 185.0ci - 225 H.P.
225CXXL 3.0L - 185.0ci - 225 H.P.
225CXXL EFI 3.0L - 185.0ci - 225 H.P.
Engine
GM 3.0L - 183ci - 6cyl
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
Model
250 (EFI)
225CXL
225CXL EFI
225CXL Seapro
225CXXL
225CXXL EFI
So my concern with writing formulas is that every row would have to be different and therefor time prohibitive. Manually copying and pasting them to the correct locations is probably a better way, at least in the instances above.
But there are times, as with the example I provided in the initial question, where I can spot a pattern (usually amongst 10 other rows that don't adhere to any pattern) Where would love to do a "FIND," and put some sort of a unique character in front of those, so I can at least use "Text to columns" to at least have Excel parse out those 4, 5 or 20 rows for me, it would be a huge time-saver.
That's why I was asking about using replace with wildcards. I don't actually want to change the data, but thought there had to be a way to use Excel to put some sort of unique character in front of the text.
For example, in doing this, I've noticed that "Kubota" engines indicate a separation between the engine and model. So I've used the "K" as my delimitater in text to columns (and later do a "Find & Replace" to put the "K" into text that now reads "ubota"
If it finds 50 "Kubota" engines out of a list of 75 or 100 rows, it saves me a lot of time as opposed to manually, it saves me a lot of time. But there has to be a way I can use Replace so the text reads "*Kubota," then if I spot other patterns like I mentioned in the initial question, then I can have it read "5400 *3-179 Diesel" "5400N *3-179 Diesel" , "5500 *4-239 Diesel" That will I can use text to columns to maybe get 75 of 100 rows, or whatever.
At the end of the day, this is always going to be a laborious, mostly manual process, but anytime I can get Excel to do most of the work for me, it will save me a lot of time down the road.
Sorry for the longer response, and I appreciate everyone's help... Just hoping if I clarify what I was looking for, somebody out there has experienced similar tasks and can hopefully offer a solution that could save me hours of work! Thanks again!
I greatly appreciate your taking the time out to answer my question.
I have already used formulas and the LEN command to parse out dates in this string, but I'm not sure if that will be the best solution for these moving forward.
Just to clarify, the list I gave was rather "idealized" since there will be literally hundreds or thousands of different items I need to break up, and they will all have a varying length, some will be one word, some will be two, etc.
Just to illustrate here's just a few rows I'm parsing out to separate columns:
Initial Text
250 (EFI) GM 3.0L - 183ci - 6cyl
225CXL 3.0L - 185.0ci - 225 H.P.
225CXL EFI 3.0L - 185.0ci - 225 H.P.
225CXL Seapro 3.0L - 185.0ci - 225 H.P.
225CXXL 3.0L - 185.0ci - 225 H.P.
225CXXL EFI 3.0L - 185.0ci - 225 H.P.
Engine
GM 3.0L - 183ci - 6cyl
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
3.0L - 185.0ci - 225 H.P.
Model
250 (EFI)
225CXL
225CXL EFI
225CXL Seapro
225CXXL
225CXXL EFI
So my concern with writing formulas is that every row would have to be different and therefor time prohibitive. Manually copying and pasting them to the correct locations is probably a better way, at least in the instances above.
But there are times, as with the example I provided in the initial question, where I can spot a pattern (usually amongst 10 other rows that don't adhere to any pattern) Where would love to do a "FIND," and put some sort of a unique character in front of those, so I can at least use "Text to columns" to at least have Excel parse out those 4, 5 or 20 rows for me, it would be a huge time-saver.
That's why I was asking about using replace with wildcards. I don't actually want to change the data, but thought there had to be a way to use Excel to put some sort of unique character in front of the text.
For example, in doing this, I've noticed that "Kubota" engines indicate a separation between the engine and model. So I've used the "K" as my delimitater in text to columns (and later do a "Find & Replace" to put the "K" into text that now reads "ubota"
If it finds 50 "Kubota" engines out of a list of 75 or 100 rows, it saves me a lot of time as opposed to manually, it saves me a lot of time. But there has to be a way I can use Replace so the text reads "*Kubota," then if I spot other patterns like I mentioned in the initial question, then I can have it read "5400 *3-179 Diesel" "5400N *3-179 Diesel" , "5500 *4-239 Diesel" That will I can use text to columns to maybe get 75 of 100 rows, or whatever.
At the end of the day, this is always going to be a laborious, mostly manual process, but anytime I can get Excel to do most of the work for me, it will save me a lot of time down the road.
Sorry for the longer response, and I appreciate everyone's help... Just hoping if I clarify what I was looking for, somebody out there has experienced similar tasks and can hopefully offer a solution that could save me hours of work! Thanks again!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 4, 2013 at 10:46 AM
May 4, 2013 at 10:46 AM
Seems to me that from one column, you want to split into two columns ( a model column and an engine column). And if I understood you, issue is how to split between engines and model.
Is having a master copy of model a possibility. The you can leverage that master list to spit the the column ?
Is having a master copy of model a possibility. The you can leverage that master list to spit the the column ?
