Text Functions التعامل مع النصوص في الاكسل

السلام عليكم و رحمة الله وبركاته

الحمد لله والصلاة والسلام على نبينا و شفيعنا محمد صلى الله عليه وسلم

المتعلقة بالنصوص function هذا الدرس هو شرح بعض أهم

A1=CODE("A")
--returns 65 (The ANSI character set)

B1=CHAR(65)
--returns the letter A

A3=EXACT(A1,A2)
--returns TRUE only if cells A1 and A2 contain exactly the same entry

A3=A1&A2
--Joining two cells (Concatenation) 

=CONCATENATE(A1,B1,C1,D1)
--Excel also has a CONCATENATE function

The TEXT function enables you to display a numeric value in a specific number format.

D3="The net profit is " & TEXT(B3," $#,##0")

--This formula displays the text along with a nicely formatted value:
The net profit is $281,252

--The following formula returns Total: $1,287.37
F1="Total: "&DOLLAR(1287.367, 2)

The REPT function repeats a text string. this seasonal formula returns HoHoHo
G1=REPT("Ho",3)

Padding a number
You’re probably familiar with a common security measure (frequently used on printed
checks) in which numbers are padded with asterisks on the right. The following formula
displays the value in cell A1, along with enough asterisks to make a total of 24 characters:

=(A1 & REPT("*",24-LEN(A1)))

If you’d prefer to pad the number with asterisks on the left instead, use this formula:
=REPT("*",24-LEN(A1))&A1

The following formula displays 12 asterisks on both sides of the number:
=REPT("*",12)&A1&REPT("*",12)

TRIM removes all leading and trailing spaces and replaces internal strings of multiple
spaces with a single space.

=TRIM(" Fourth  Quarter  Earnings ")

--returns the number of characters
B1=LEN(A1)

Changing the case of text
Excel provides three handy functions to change the case of text:
■ UPPER converts the text to ALL UPPERCASE.
■ LOWER converts the text to all lowercase.
■ PROPER converts the text to Proper Case (the first letter in each word is capitalized, as in a proper name).

B1=PROPER(A1)
C1=UPPER(A1)
D1=LOWER(A1)

Extracting characters from a string

■ LEFT returns a specified number of characters from the beginning of a string.
■ RIGHT returns a specified number of characters from the end of a string.
■ MID returns a specified number of characters beginning at a specified position within a string

The following formula returns the last ten characters from cell A1; if A1 contains fewer
than ten characters, the formula returns all text in the cell:
B1=RIGHT(A1,10)

This next formula uses the MID function to return five characters from cell A1, beginning
at character position 2. In other words, it returns characters 2 through 6.
=MID(A1,2,5)

Replacing text with other text

■ SUBSTITUTE replaces specific text in a string. Use this function when you know the character(s) to be replaced but not the position.
■ REPLACE replaces text that occurs in a specific location within a string. Use this function when you know the position of the text to be replaced but not the actual text.

=SUBSTITUTE("2015 Budget","2015","2016")
=REPLACE("Part-544",5,1,"")

Finding and searching within a string

■ FIND fi nds a substring within another text string and returns the starting position
of the substring. You can specify the character position at which to begin searching.
Use this function for case-sensitive text comparisons. Wildcard comparisons
are not supported.
■ SEARCH fi nds a substring within another text string and returns the starting position
of the substring. You can specify the character position at which to begin
searching. Use this function for non-case-sensitive text or when you need to use
wildcard characters.

=FIND("m","the position of the first m in the string",1)
=SEARCH("m","the position of the first m (either uppercase or lowercase)",1)