Replace charecter string with leading character Closed

Question

Hello, 

I'm working on an extremely large catalog project where I'm basically taking one column of text and breaking it up into several different columns on the same row. Unfortunately, there no obvious delimited characters and the length of each entry vary, so "Text to columns" is not an immediate, easy option.

However, I am seeing several "trends" with different character strings, and am working on a way to "trick" Excel into adding a delimited character, and then using that character to move my data into the next column.

For example, in the following rows:

5400 3-179 Diesel 
5400N 3-179 Diesel 
5500 4-239 Diesel 

I can see that everything to the left of the 3-179 and 4-239 belongs in one column, and the 3-179 Diesel and 4-239 belongs in another.

I know how to do a "FIND" using wildcards where"?-???" would find all of the relevant strings, but how do I use "replace" so it will put a "*" (or any other unique character) in front of the 3 or 4 but leave the rest of the string in-tact?

This is just one of hundreds (thousands?) of different patterns I'll probably come across so any time I can "trick" Excel into converting multiple rows at a time, rather than me manually breaking up the different columns row-by-row, I could literally shave hours, days or weeks off the length of the project.

Thanks,
David

Configuration: Windows 7 / Internet Explorer 8.0

venkat1926 · Answer

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.

Kevin@Radstock · Answer

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

davidplowman · Answer

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!

Replace charecter string with leading character

3 responses

Similar discussions

Subscribe To Our Newsletter!