Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 361
Default Put A Space Between 2 right characters

I have some symbols that look like this

ABC
ABCD
ABCDE

I need a formula that can make this conversion:

A_BC (1 spaces between A and BC)
AB_CD (1 space between AB and CD)
ABC_DE (1 space between ABC and DE)

So I always need the 2 right most characters separated by a space from the
other characters.

I hope I explained this ok.

Thank you in advance.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Put A Space Between 2 right characters

Try this:

=LEFT(A1,LEN(A1)-2)&"_"&RIGHT(A1,2)

assuming your text is in A1. Copy down as required.

Hope this helps.

Pete

On Apr 8, 2:03*pm, carl wrote:
I have some symbols that look like this

ABC
ABCD
ABCDE

I need a formula that can make this conversion:

A_BC (1 spaces between A and BC)
AB_CD (1 space between AB and CD)
ABC_DE (1 space between ABC and DE)

So I always need the 2 right most characters separated by a space from the
other characters.

I hope I explained this ok.

Thank you in advance.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Put A Space Between 2 right characters

=LEFT(A1,LEN(A1)-2) & " " & RIGHT(A1,2)

--
Gary''s Student - gsnu200777


"carl" wrote:

I have some symbols that look like this

ABC
ABCD
ABCDE

I need a formula that can make this conversion:

A_BC (1 spaces between A and BC)
AB_CD (1 space between AB and CD)
ABC_DE (1 space between ABC and DE)

So I always need the 2 right most characters separated by a space from the
other characters.

I hope I explained this ok.

Thank you in advance.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Put A Space Between 2 right characters

Or try this......

=REPLACE(A1,LEN(A1)-1,," ")

Regards
Bosco

"Pete_UK" wrote:

Try this:

=LEFT(A1,LEN(A1)-2)&"_"&RIGHT(A1,2)

assuming your text is in A1. Copy down as required.

Hope this helps.

Pete

On Apr 8, 2:03 pm, carl wrote:
I have some symbols that look like this

ABC
ABCD
ABCDE

I need a formula that can make this conversion:

A_BC (1 spaces between A and BC)
AB_CD (1 space between AB and CD)
ABC_DE (1 space between ABC and DE)

So I always need the 2 right most characters separated by a space from the
other characters.

I hope I explained this ok.

Thank you in advance.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Characters with space in a cell NH Excel Discussion (Misc queries) 5 April 5th 07 05:07 AM
How do I display white space characters in excel? Mac Excel Discussion (Misc queries) 1 March 7th 07 09:44 PM
Clean non printable characters and replace with space rtremblay Excel Worksheet Functions 8 December 2nd 06 12:10 AM
cHARACTERS BEFORE THE SPACE T De Villiers Excel Worksheet Functions 3 January 19th 06 02:22 AM
Remove varying amounts of space characters Access Joe Excel Discussion (Misc queries) 4 January 13th 06 11:28 PM


All times are GMT +1. The time now is 03:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"