Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
serene83
 
Posts: n/a
Default HELP on "left","right","find","len","substitute" functions


Hi, i have a problem here which i need to use "left", "right", "find",
"len" and "substitute" function to solve it.

How do i use left", "right", "find", "len" and "substitute" function to
split these car models into two columns?

Question:

Honda Accord
Toyota Camry
Mitsubishi Galant
BMW 3 Series

Ans:

1st column:
Honda
Toyota
Mtsubishi
BMW

2nd column:
Accord
Camry
Galant
3 Series

Thank you.


--
serene83
------------------------------------------------------------------------
serene83's Profile: http://www.excelforum.com/member.php...o&userid=35778
View this thread: http://www.excelforum.com/showthread...hreadid=555812

  #2   Report Post  
Posted to microsoft.public.excel.misc
galimi
 
Posts: n/a
Default HELP on "left","right","find","len","substitute" functions

Use the following formula to obtain the portion to the left of the space

=LEFT(A1,FIND(" ",A1))

and for the right

=RIGHT(A1,LEN(A1)-FIND(" ",A1))
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"serene83" wrote:


Hi, i have a problem here which i need to use "left", "right", "find",
"len" and "substitute" function to solve it.

How do i use left", "right", "find", "len" and "substitute" function to
split these car models into two columns?

Question:

Honda Accord
Toyota Camry
Mitsubishi Galant
BMW 3 Series

Ans:

1st column:
Honda
Toyota
Mtsubishi
BMW

2nd column:
Accord
Camry
Galant
3 Series

Thank you.


--
serene83
------------------------------------------------------------------------
serene83's Profile: http://www.excelforum.com/member.php...o&userid=35778
View this thread: http://www.excelforum.com/showthread...hreadid=555812


  #3   Report Post  
Posted to microsoft.public.excel.misc
CLR
 
Posts: n/a
Default HELP on "left","right","find","len","substitute" functions

With your data in column A, put this in column B and copy down
=LEFT(A1,FIND(" ",A1,1)-1)
and this in column C and copy down
=MID(A1,FIND(" ",A1,1)+1,99)

Vaya con Dios,
Chuck, CABGx3



in 5c"serene83"
wrote in message
news:solum5t6erene83.2a1c0c_1151368502.4853@excelf orum-nospam.com...

Hi, i have a problem here which i need to use "left", "right", "find",
"len" and "substitute" function to solve it.

How do i use left", "right", "find", "len" and "substitute" function to
split these car models into two columns?

Question:

Honda Accord
Toyota Camry
Mitsubishi Galant
BMW 3 Series

Ans:

1st column:
Honda
Toyota
Mtsubishi
BMW

2nd column:
Accord
Camry
Galant
3 Series

Thank you.


--
serene83
------------------------------------------------------------------------
serene83's Profile:

http://www.excelforum.com/member.php...o&userid=35778
View this thread: http://www.excelforum.com/showthread...hreadid=555812



  #4   Report Post  
Posted to microsoft.public.excel.misc
Ragdyer
 
Posts: n/a
Default HELP on "left","right","find","len","substitute" functions

Sounds like homework to me.

Will your teacher mark you down since none of the suggestions here used
"substitute"?

Ask for extra credit since the "mid" function was used instead.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"serene83" wrote in
message ...

Hi, i have a problem here which i need to use "left", "right", "find",
"len" and "substitute" function to solve it.

How do i use left", "right", "find", "len" and "substitute" function to
split these car models into two columns?

Question:

Honda Accord
Toyota Camry
Mitsubishi Galant
BMW 3 Series

Ans:

1st column:
Honda
Toyota
Mtsubishi
BMW

2nd column:
Accord
Camry
Galant
3 Series

Thank you.


--
serene83
------------------------------------------------------------------------
serene83's Profile:
http://www.excelforum.com/member.php...o&userid=35778
View this thread: http://www.excelforum.com/showthread...hreadid=555812


  #5   Report Post  
Posted to microsoft.public.excel.misc
Franz Verga
 
Posts: n/a
Default HELP on "left","right","find","len","substitute" functions

Nel post
*serene83* ha scritto:

Hi, i have a problem here which i need to use "left", "right", "find",
"len" and "substitute" function to solve it.

How do i use left", "right", "find", "len" and "substitute" function
to split these car models into two columns?

Question:

Honda Accord
Toyota Camry
Mitsubishi Galant
BMW 3 Series

Ans:

1st column:
Honda
Toyota
Mtsubishi
BMW

2nd column:
Accord
Camry
Galant
3 Series

Thank you.


If your data are in column A, starting from A1, enter this in B1:

=LEFT(A1,FIND(" ",A1)-1)

and this in C1:

=RIGHT(A1,LEN(A1)-LEN(B1)-1)

select B1:C1 and copy down.

--

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy




  #6   Report Post  
Posted to microsoft.public.excel.misc
serene83
 
Posts: n/a
Default HELP on "left","right","find","len","substitute" functions


thanks for all the help. This is not a homework, juz something which i
found in a book.


--
serene83
------------------------------------------------------------------------
serene83's Profile: http://www.excelforum.com/member.php...o&userid=35778
View this thread: http://www.excelforum.com/showthread...hreadid=555812

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
Can I get Excel to determine the line curve formula without graph. Cadelima Excel Discussion (Misc queries) 8 December 20th 05 10:57 PM
User-defined functions created in Excel 2000 fail in Excel 2003 goodguy Excel Discussion (Misc queries) 1 October 3rd 05 07:04 PM
Database functions should use criteria in formula, as 1-2-3 does 123user Excel Worksheet Functions 8 September 29th 05 08:57 PM
Visible rows and functions that work tracy Excel Worksheet Functions 2 August 19th 05 05:25 AM
PASTE DOWN FUNCTIONS jackle Excel Worksheet Functions 0 May 25th 05 02:10 PM


All times are GMT +1. The time now is 09:48 AM.

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"