How to replace just a single character from many of the same one within a text?

dvpe

New Member
Joined
Nov 11, 2010
Messages
6
Hi folks, I have to deal with a text database into Excel but ended up finding irregular format that need to be fixed.
As it can be seen within the text right below, there are space delimiters in between the words. I want to replace always the 2nd space from right to left with a semicolon.

20/10/2012 CENTAURO CE 39 (06/10) 57.97 0.00
20/10/2012 CENTAURO CEFT 534 (09/10) 1,235.34 0.56
20/10/2012 CENTAURO (06/10) 5,345,200.00 45.00 1.01
20/10/2012 TFRE (06/10) 1.00 0.00
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
Assuming text is in cell A2, formula is:
=SUBSTITUTE(A2," ",";",(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))
 
Upvote 0

dulitul

Board Regular
Joined
Jan 19, 2013
Messages
193
Hey,

Provided the text is in A1 then this is the formula you need. It will replace 2nd space from right to left with a semicolon.

=SUBSTITUTE(A1," ","; ",2)
 
Upvote 0

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
fghIjklmnopqrstuvwxyzaaabacadaeafagahaiajAK
123456789101112131415161718192021222324252627282930MAX
ab cdef ghi j k lmn3812141614row 5
cell I4 contains the formulaIF(MID($F5,I4,1)=" ",I4,"")
cell AK5 contains the formulaLARGE(G5:AJ5,2)



I did not know about the substitute formula and started to attack it from first principles - so I post this ti give you all a laugh
thus you have located the second space in from the right



<colgroup><col><col span="34"><col></colgroup><tbody>
</tbody>
 
Upvote 0

dvpe

New Member
Joined
Nov 11, 2010
Messages
6
Hey,

Provided the text is in A1 then this is the formula you need. It will replace 2nd space from right to left with a semicolon.

=SUBSTITUTE(A1," ","; ",2)

It wasn't the path I meant but I got the logical idea. Nice. Thanks a lot.
 
Upvote 0

Forum statistics

Threads
1,195,858
Messages
6,011,983
Members
441,661
Latest member
Pammie007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top