Formulas
 

I get that the following forumla can tell me the position of the first space from the right. Can I make it replace that space with a comma or semicolon or something? I understand this... JUST enough to be dangerous but cant seem to make something that will perform this duty. i would really really appreciate it!

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0))

Formulas
 

Hi,

Am Mon, 30 Sep 2013 13:11:49 -0700 (PDT) schrieb :

I get that the following forumla can tell me the position of the first space from the right. Can I make it replace that space with a comma or semicolon or something? I understand this... JUST enough to be dangerous but cant seem to make something that will perform this duty. i would really really appreciate it!

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0))

replace the first space from left:
=SUBSTITUTE(A1," ",",",1)
replace the first space from right:
=SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",)))


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Formulas
 

OH! Man, dang it, i was trying to adjust the original forumla to do it, this is much simpler... I really need to find a book on these forumlas...

Then all i had to do was copy paste that column and delimit it!

THANK YOU!

On Monday, September 30, 2013 2:25:51 PM UTC-6, Claus Busch wrote:
Hi,



Am Mon, 30 Sep 2013 13:11:49 -0700 (PDT) schrieb :



I get that the following forumla can tell me the position of the first space from the right. Can I make it replace that space with a comma or semicolon or something? I understand this... JUST enough to be dangerous but cant seem to make something that will perform this duty. i would really really appreciate it!



=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-0))



replace the first space from left:

=SUBSTITUTE(A1," ",",",1)

replace the first space from right:

=SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",)))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2